Badges for 10 and 100 wiki edits across topics

[nathank (Nathan Kershaw)]

Continuing the discussion from The default badge queries:

@JammyDodger

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?

[JammyDodger]

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?)

[nathank (Nathan Kershaw)]

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

[JammyDodger]

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

[JammyDodger]

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.