Continuing the discussion from The default badge queries: [image]The default badge queries :3rd_place_medal: Wiki Editor SELECT pr2.user_id, pr2.post_id, pr2.created_at granted_at FROM ( SELECT MIN(pr.id) id FROM post_revisions pr JOIN badge_posts p on p.id = pr.post_id WHERE p.wiki AND NOT pr.hidden AND (:backfill OR p.id IN (:post_ids)) GROUP BY pr.user_id ) as X JOIN post_revisions pr2 ON pr2.id = X.id I’m trying to make a silver badge for 10 wiki edits (across all posts) and a gold badge for 100 wiki edits - however neither I nor the Forum Helper are getting anywhere. Got any suggestions?

I’ve been playing with this kind of format to avoid the MIN/MAX issue of ascribing the wrong granted_at date when bringing a badge in ‘mid-flow’ (they tend to work better when people haven’t already achieved the criteria before the badge is introduced). So possibly something like this: SELECT user_id, created_at granted_at FROM ( SELECT pr.user_id, pr.created_at, ROW_NUMBER() OVER (PARTITION BY pr.user_id ORDER BY pr.created_at) as row_number FROM post_revisions pr INNER JOIN badge_posts p ON p.id = pr.post_id WHERE p.wiki = TRUE AND pr.hidden = FALSE AND pr.user_id > 0 ) AS revisions WHERE row_number = 100 (I’ve not included the usual ‘don’t include deleted topics or posts’ as edits to old wikis should probably still count. I’ve not added the backfill either, but I think this one would be ‘update daily’ as the trigger?)

Nice! Is it for doing 100 edits across wiki posts, or editing 100 wiki posts?

After a little extra thought (and a small conflab with Bert :slight_smile:) I think ROW_NUMBER would be the better choice for this one. I’ll edit the query above. :+1:

Badges for 10 and 100 wiki edits across topics

Community Building Data & reporting

JammyDodger January 11, 2024, 7:24pm 4

That’s 100 post revisions of wikis posts, so editing one a hundred times (or a hundred once) would pop the badge.

Topic		Replies	Views
Badges for posting Data & reporting sql-triggered-badge	4	595	August 27, 2024
A badge granted when someone posts a reply in a single specified topic Data & reporting sql-triggered-badge	7	534	June 19, 2018
How do you get the Wiki Editor badge? Product Support	1	1520	May 21, 2019
Custom 'Scribe' badge Data & reporting sql-triggered-badge	0	370	August 27, 2023
Badge for popular topic Data & reporting sql-triggered-badge	5	489	June 1, 2024

Badges for 10 and 100 wiki edits across topics

Related topics