I want to get the users with most topics created in each tag category.
For example the list of users with most posts on this community using the data-explorer tag.
I tried a lot with my rusty SQL skills but couldn’t get it right, any help is appreciated!
-- [params]
-- string :tag_name
SELECT t.user_id,
count(t.user_id) AS count
FROM topic_tags tt
JOIN topics t ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tg.name = :tag_name
GROUP BY t.user_id
ORDER BY count DESC
LIMIT 10