Badges for 10 and 100 wiki edits across topics

Community Building Data & reporting
,
2

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