No. It’s to make them self documenting, since those particular badges on our instance are based on 3^3, 3^4, 3^5, 3^6 etc, (i.e. because 9, 27, 81, 243 and higher are less readable when glancing at the query.)
If you’re basing yours on powers of 10, then simply use 10000, 20000, 30000 or whatever.
Yes, but
- Do you mean badges for one single topic?
- All topics within a single category?
If the first, then that’s easy, the topic_id is in post_timings:
SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
AND topic_id=<TOPICNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10) /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC
But you’d need a separate set of badges for each topic you want to count posts in.
If it’s the second - all topics within a category then you’re going to need to join against the topics table to get the category_id you’re looking for:
SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
JOIN topics t on t.id=pt.topic_id
WHERE :backfill
AND t.category_id=<CATEGORYNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10) /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC
Ditto for the above though - you’d need a separate set of badges for each category you want badges for (unless you do AND t.category_id in (<CATNO1>, <CATNO2>, <CATNO3>))
Note, however, that these queries are expensive in the time they take to execute. Run them at most once a day.
