[image]Finding 'Top X' users with the most badges Test this: -- [params] -- int :posts = 100 -- int :top = 10 SELECT u.username, count(ub.id) as "Badges" FROM user_badges ub, users u, user_stats us WHERE u.id = ub.user_id AND u.id = us.user_id AND us.post_count > :posts AND (u.admin = 'f' AND u.moderator = 'f') GROUP BY u.username ORDER BY count(ub.id) desc LIMIT :top How can I filter a specific badge? I need to count how many times they achieved it Es. “howto writer badge” I need to know who wrote more howtos and create a ranking

Here’s a slightly modified version of that query that adds a badge name filter. The badge name defaults to 'all badges'. When set to that value, results for all badges will be returned. If you set the badge name to the name of a specific badge, only results for that badge will be returned. -- [params] -- int :posts = 1 -- int :top = 10 -- string :badge_name = all badges SELECT username, COUNT(ub.id) as badge_count FROM user_badges ub JOIN users u ON u.id = ub.user_id JOIN user_stats us ON us.user_id = ub.user_id JOIN badges b ON b.id = ub.badge_id WHERE us.post_count > :posts AND (u.admin = 'f' AND u.moderator = 'f') AND CASE WHEN 'all badges' = :badge_name THEN true ELSE b.name = :badge_name END GROUP BY u.username ORDER BY badge_count DESC

That’s it :slight_smile: thank you so much

User badge counts with badge name filter

Community Building Data & reporting

alefattorini (Alessio Fattorini) October 22, 2020, 2:38pm 2

Any hint on this? I tried to add a line
AND b.id = 136

But it doesn’t work

Topic		Replies	Views
Finding 'Top X' users with the most badges Data & reporting badges , sql-query	5	255	December 23, 2018
Count of badges on user page? Feature	6	1004	August 11, 2022
Badge multiple wording - "Yeah, but how many do _I_ have?" Feature ux , fixed	1	171	October 12, 2026
An extra badge based on already granted badges number? Data & reporting sql-triggered-badge	1	737	May 23, 2023
Badges for posting Data & reporting sql-triggered-badge	4	592	August 27, 2024

User badge counts with badge name filter

Related topics