Sure I’d already posted this query, but… this is the one for the top 10%-25% (I have comparable queries for 5%-10%(silver), and top 5%(gold) and top poster (again gold but CSS’d to be a brown spoon)).
Here’s a short version that most sites could probably use:
WITH LastMonth AS ( /* Count eligible posts from last month */
SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
FROM badge_posts bp
JOIN users u on u.id=bp.user_id
WHERE bp.created_at > CURRENT_DATE - INTERVAL '1 month'
GROUP BY u.username, bp.user_id
HAVING count(*) > 1
ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS ( /* Total relevant users that have posted in the last month */
SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.10 AND /* 10% - change this*/
row_number < TotalUsers.max *.25 /* 25% - change this */
Our site has people who like gaming the system so, so our actual query (presented below) has a few extra clauses that
- Exclude particular topics (over and above those excluded normally from
badge_posts) such as topics created solely for the purpose of encouraging excessive posting
- Excludes particular ‘users’ (actually 'bots - they don’t get this badge)
- Excludes particularly short topics:
WITH exclusions AS ( /* Which other topics to exclude from counters */
SELECT user_id, id, topic_id, post_number
FROM posts
WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE group_id IN(
SELECT g.id
FROM groups g
WHERE g.name IN ('admins')
)
)
),
LastMonth AS ( /* Count eligible posts from last month */
SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
FROM badge_posts bp
JOIN users u on u.id=bp.user_id AND
bp.user_id NOT IN ( /* ignore bots */
SELECT gu.user_id
FROM group_users gu
WHERE group_id IN(
SELECT g.id
FROM groups g
WHERE g.name IN ('bots')
)
)
WHERE topic_id NOT IN ( /* exclude short topics */
SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10
) AND topic_id NOT IN ( /* Ineligible topics */
SELECT topic_id
FROM exclusions
) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
GROUP BY u.username, bp.user_id
HAVING count(*) > 1
ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.1 AND
row_number < TotalUsers.max *.25
this - thanks for sharing! Works for me.