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?

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

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 12, 2024, 11:20am 5

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

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