# Badge for popular topic

**URL:** <https://meta.discourse.org/t/badge-for-popular-topic/309938>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [May 30, 2024, 7:03am UTC](https://meta.discourse.org/t/badge-for-popular-topic/309938 "2024-05-30T07:03:51Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [May 30, 2024, 8:21am UTC](https://meta.discourse.org/t/badge-for-popular-topic/309938/2 "2024-05-30T08:21:41Z")

</div>

I think a simple version would be something like this:

```sql
SELECT 
    t.user_id,
    CURRENT_TIMESTAMP AS granted_at,
    p.id AS post_id
FROM topics t
  JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.posts_count >= 100
  AND t.archetype = 'regular'
  AND t.deleted_at ISNULL
  AND t.user_id > 0

```

Using the `t.post_counts` would currently also include the small action posts (closed, unlisted, etc), so it depends on how truly accurate you want it to be.

You could make it more specfic by doing a count of the ‘eligible’ posts within the query. Something like:

```sql
SELECT 
    t.user_id,
    CURRENT_TIMESTAMP AS granted_at,
    p.id AS post_id
FROM topics t
  JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.id IN ( 
    SELECT 
        p.topic_id
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.archetype = 'regular'
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND p.post_number <> 1
      AND p.post_type = 1
      AND p.hidden IS FALSE
     GROUP BY 1
     HAVING COUNT(*) >= 100
     )

```

---

_[View the full topic](https://meta.discourse.org/t/badge-for-popular-topic/309938)._