# Badges for reading \[ ⚠ intensive\]

**URL:** <https://meta.discourse.org/t/badges-for-reading-intensive/276681>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [2015 年 2 月 14 日午後 3:28 UTC](https://meta.discourse.org/t/badges-for-reading-intensive/276681 "2015-02-14T15:28:17Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![PJH](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pjh/32/39071_2.png) [@PJH](https://meta.discourse.org/u/PJH)\
**Post date:** [2015 年 8 月 5 日午前 10:44 UTC](https://meta.discourse.org/t/badges-for-reading-intensive/276681/3 "2015-08-05T10:44:37Z")

</div>

> [@alefattorini](#):
>
> Why are you using pow? Is it faster than say just 20000 posts?

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.

> [@alefattorini](#):
>
> Can I do the same thing for **whole topics**?

Yes, but

1. 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`:

```plaintext
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:

```plaintext
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.

---

_[View the full topic](https://meta.discourse.org/t/badges-for-reading-intensive/276681)._