# The default badge queries

**URL:** https://meta.discourse.org/t/the-default-badge-queries/276812
**Category:** Data & reporting
**Tags:** reference, sql-triggered-badge
**Created:** [August 27, 2023, 9:09am UTC](https://meta.discourse.org/t/the-default-badge-queries/276812 "2023-08-27T09:09:00Z")
**Posts on this page:** 1
**Showing post:** 1

<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: [August 27, 2023, 9:09am UTC](https://meta.discourse.org/t/the-default-badge-queries/276812/1 "2023-08-27T09:09:00Z")

</div>

This is a reference guide for the SQL queries of the default badges, and their trigger information (where available).

## Core Badges

### 🥈 Anniversary

(This one has some extra backend magic in to choose the dates, but I’ll include it anyway)

```sql
 start_date = start_date.iso8601(6)
    end_date = end_date.iso8601(6)

      SELECT u.id
        FROM users AS u
        JOIN posts AS p ON p.user_id = u.id
        JOIN topics AS t ON p.topic_id = t.id
       WHERE u.id > 0
         AND u.active
         AND NOT u.staged
         AND (u.silenced_till IS NULL OR u.silenced_till < '#{start_date}')
         AND (u.suspended_till IS NULL OR u.suspended_till < '#{start_date}')
         AND u.created_at <= '#{start_date}'
         AND NOT p.hidden
         AND p.deleted_at IS NULL
         AND p.created_at BETWEEN '#{start_date}' AND '#{end_date}'
         AND t.visible
         AND t.archetype <> 'private_message'
         AND t.deleted_at IS NULL
         AND NOT EXISTS (SELECT 1 FROM user_badges AS ub WHERE ub.user_id = u.id AND ub.badge_id = #{Badge::Anniversary} AND ub.granted_at BETWEEN '#{start_date}' AND '#{end_date}')
         AND NOT EXISTS (SELECT 1 FROM anonymous_users AS au WHERE au.user_id = u.id)
       GROUP BY u.id
      HAVING COUNT(p.id) > 0

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Update daily |

### 🥉 Appreciated (number of Likes on multiple posts)

The 🥉 Appreciated, 🥈 Respected and 🥇 Admired badges follow the same pattern but with different values for `p.like_count` and `HAVING COUNT(*)`.

```sql
SELECT p.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM posts AS p
      WHERE p.like_count >= #{like_count}
        AND (:backfill OR p.user_id IN (:user_ids))
      GROUP BY p.user_id
      HAVING COUNT(*) > #{post_count}

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Update daily |

  

### 🥉 Autobiographer

```sql
SELECT u.id user_id, CURRENT_TIMESTAMP granted_at
    FROM users u
    JOIN user_profiles up on u.id = up.user_id
    WHERE bio_raw IS NOT NULL AND LENGTH(TRIM(bio_raw)) > 10 AND
          uploaded_avatar_id IS NOT NULL AND
          (:backfill OR u.id IN (:user_ids) )

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user is edited or created |

  

### 🥉 Basic (Trust Levels)

The 🥉 Basic, 🥉 Member, 🥈 Regular, 🥇 Leader badges all follow the same pattern but with a different `trust_level` value.

```sql
SELECT u.id user_id, current_timestamp granted_at FROM users u
      WHERE trust_level >= #{level.to_i} AND (
        :backfill OR u.id IN (:user_ids)
      )

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user changes trust level |

  

### 🥉 Certified and 🥈 Licensed

These have **no SQL query**. They are part of Discourse Narrative Bot and are granted programmatically when a user completes the interactive tutorials (Discobot).

  

### 🥉 Editor

```sql
 SELECT p.user_id, min(p.id) post_id, min(p.created_at) granted_at
    FROM badge_posts p
    WHERE p.self_edits > 0 AND
        (:backfill OR p.id IN (:post_ids) )
    GROUP BY p.user_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user edits or creates a post |

  

### 🥉 Enthusiast

The 🥉 Enthusiast, 🥈 Aficionado, and 🥇 Devotee badges follow the same pattern but with a different `HAVING COUNT(*)` threshold.

```sql
WITH consecutive_visits AS (
        SELECT user_id
             , visited_at
             , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
          FROM user_visits
      ), visits AS (
        SELECT user_id
             , MIN(visited_at) "start"
             , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank"
          FROM consecutive_visits
      GROUP BY user_id, s
        HAVING COUNT(*) >= #{days}
      )
      SELECT user_id
           , "start" + interval '#{days} days' "granted_at"
        FROM visits
       WHERE "rank" = 1

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Update daily |

  

### 🥉 First Emoji

There is no SQL query — the badge is awarded the moment a qualifying post is processed via [`CookedPostProcessor#grant_badges`](https://github.com/discourse/discourse/blob/main/lib/cooked_post_processor.rb#L59-L70).

**Criterion:** The cooked post must contain at least one `img.emoji` element that is **not** inside a `aside.quote` block. In other words, emojis typed directly into the post body count; emojis that appear only inside a quoted passage do not.

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | ✅ |
| Trigger | Post cook (CookedPostProcessor) |

### 🥉 First Flag

```sql
SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id IN (
        SELECT f.id
        FROM flags f
        WHERE name != 'like'
        AND score_type IS FALSE
        AND require_message IS FALSE
      )
      AND (:backfill OR pa.post_id IN (:post_ids))
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | ✅ |
| Trigger | When a user acts on a post |

  

### 🥉 First Like

```sql
SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user acts on a post |

  

### 🥉 First Link

```sql
SELECT l.user_id, l.post_id, l.created_at granted_at
    FROM
    (
      SELECT MIN(l1.id) id
      FROM topic_links l1
      JOIN badge_posts p1 ON p1.id = l1.post_id
      JOIN badge_posts p2 ON p2.id = l1.link_post_id
      WHERE NOT reflection AND p1.topic_id <> p2.topic_id AND not quote AND
        (:backfill OR ( p1.id in (:post_ids) ))
      GROUP BY l1.user_id
    ) ids
    JOIN topic_links l ON l.id = ids.id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user edits or creates a post |

  

### 🥉 First Mention

```sql
SELECT acting_user_id AS user_id, MIN(target_post_id) AS post_id, MIN(p.created_at) AS granted_at
    FROM user_actions
    JOIN posts p ON p.id = target_post_id
    JOIN topics t ON t.id = topic_id
    JOIN categories c on c.id = category_id
    WHERE action_type = 7
      AND NOT read_restricted
      AND p.deleted_at IS NULL
      AND t.deleted_at IS NULL
      AND t.visible
      AND t.archetype <> 'private_message'
      AND (:backfill OR p.id IN (:post_ids))
    GROUP BY acting_user_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user edits or creates a post |

  

### 🥉 First Onebox

There is no SQL query. Granted during post cooking via [`CookedPostProcessor#grant_badges`](https://github.com/discourse/discourse/blob/main/lib/cooked_post_processor.rb#L63-L65).

**Criterion:** The cooked post must have produced at least one onebox — a rich link preview generated when a bare URL on its own line resolves to an embeddable resource.

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | ✅ |
| Trigger | Post cook (`CookedPostProcessor`) |

### 🥉 First Quote

```sql
SELECT ids.user_id, q.post_id, p3.created_at granted_at
    FROM
    (
      SELECT p1.user_id, MIN(q1.id) id
      FROM quoted_posts q1
      JOIN badge_posts p1 ON p1.id = q1.post_id
      JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
      WHERE (:backfill OR ( p1.id IN (:post_ids) ))
      GROUP BY p1.user_id
    ) ids
    JOIN quoted_posts q ON q.id = ids.id
    JOIN badge_posts p3 ON q.post_id = p3.id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user edits or creates a post |

  

### 🥉 First Reply-by-Email

There is no SQL query. Granted during post cooking via [`CookedPostProcessor#grant_badges`](https://github.com/discourse/discourse/blob/main/lib/cooked_post_processor.rb#L66-L68).

**Criterion:** `post.is_reply_by_email?` must be `true` — that is, the post was submitted by replying to a Discourse notification email rather than through the web interface. Requires the incoming email reply feature to be enabled on the site.

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | ✅ |
| Trigger | Post cook (`CookedPostProcessor`) |

Original community suggestion ['Reply by email' badge - #3 by lrossouw](https://meta.discourse.org/t/reply-by-email-badge/276733/3)

### 🥉 First Share

```sql
SELECT views.user_id, i2.post_id, i2.created_at granted_at
    FROM
    (
      SELECT i.user_id, MIN(i.id) i_id
      FROM incoming_links i
      JOIN badge_posts p on p.id = i.post_id
      JOIN users u on u.id = i.user_id
      GROUP BY i.user_id
    ) as views
    JOIN incoming_links i2 ON i2.id = views.i_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | Update daily |

### 🥉 New User of the Month

There is no SQL query registered against the badge. Granted by a **daily scheduled job** ([`Jobs::GrantNewUserOfTheMonthBadges`](https://github.com/discourse/discourse/blob/main/app/jobs/scheduled/grant_new_user_of_the_month_badges.rb)). The job runs every day but only awards for the previous calendar month, and only once per month.

**Eligibility:** The candidate must have created their account _during the previous calendar month_, be active and non-staged, not be an admin or moderator, and not be suspended. They must also have posted in at least **2 distinct topics** with at least **2 posts** total, and received at least **2 likes**.

**Scoring:** Candidates are ranked by a weighted-like score. Each like received is weighted by the trust level of the person who gave it:

| Liker | Weight |
| --- | --- |
| Admin or Moderator | 3.0 |
| Trust Level 4 | 2.0 |
| Trust Level 3 | 1.5 |
| Trust Level 2 | 1.0 |
| Trust Level 1 | 0.25 |
| Trust Level 0 | 0.1 |

The final score is `SUM(weighted likes) / (5 + post count)` — dividing by post count dampens the advantage of prolific posters. Up to **2 users** can receive the badge per month. Each winner also receives a system message.

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Daily scheduled job (awards for previous calendar month) |

  

### 🥉 Nice Reply (Likes on a Post)

The 🥉 Nice Reply, 🥈 Good Reply, and 🥇 Great Reply badges all follow the same pattern but with a different threshold for `p.like_count`.

```sql
SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number > 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user acts on a post |

  

### 🥉 Nice Share (Link Sharing)

The 🥉 Nice Share, 🥈 Good Share, and 🥇 Great Share badges al follow the same pattern but with a different `HAVING COUNT(*)` threshold.

```sql
SELECT views.user_id, i2.post_id, CURRENT_TIMESTAMP granted_at
      FROM
      (
        SELECT i.user_id, MIN(i.id) i_id
        FROM incoming_links i
        JOIN badge_posts p on p.id = i.post_id
        JOIN users u on u.id = i.user_id
        GROUP BY i.user_id,i.post_id
        HAVING COUNT(DISTINCT(i.ip_address, i.current_user_id)) >= #{count}
      ) as views
      JOIN incoming_links i2 ON i2.id = views.i_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | Update daily |

  

### 🥉 Nice Topic (Likes on a Topic)

The 🥉 Nice Topic, 🥈 Good Topic, and 🥇 Great Topic badges all follow the same pattern but with a different threshold for `p.like_count`.

```sql
SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number = 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user acts on a post |

  

### 🥉 Out of Love (Max Likes in a day)

The 🥉 Out of Love, 🥈 Higher Love, and 🥇 Crazy in Love badges all follow the same pattern but with a different value `HAVING COUNT(*)` threshold.

```sql
SELECT gdl.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM given_daily_likes AS gdl
      WHERE gdl.limit_reached
        AND (:backfill OR gdl.user_id IN (:user_ids))
      GROUP BY gdl.user_id
      HAVING COUNT(*) >= #{count}

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Update daily |

  

### 🥉 Popular Link (Link Clicks)

The 🥉 Popular Link, 🥈 Hot Link, and 🥇 Famous Link all follow the same pattern but with a different `tl.clicks` threshold.

```sql
SELECT tl.user_id, post_id, CURRENT_TIMESTAMP granted_at
        FROM topic_links tl
        JOIN badge_posts p ON p.id = post_id
       WHERE NOT tl.internal
         AND tl.clicks >= #{count}
      GROUP BY tl.user_id, tl.post_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | Update daily |

  

### 🥉 Promoter (Invites)

The 🥉 Promoter, 🥈 Campaigner, and 🥇 Champion badges all follow the same pattern but with a different Trust Level value needed for the invitees, and a different `HAVING COUNT(*)` threshold.

```sql
SELECT u.id user_id, CURRENT_TIMESTAMP granted_at
      FROM users u
      WHERE u.id IN (
        SELECT invited_by_id
        FROM invites i
        JOIN invited_users iu ON iu.invite_id = i.id
        JOIN users u2 ON u2.id = iu.user_id
        WHERE i.deleted_at IS NULL
        AND i.invited_by_id <> u2.id
        AND u2.active
        AND u2.trust_level >= #{trust_level.to_i}
        AND u2.silenced_till IS NULL
        GROUP BY invited_by_id
        HAVING COUNT(*) >= #{count.to_i}
      ) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
      (:backfill OR u.id IN (:user_ids) )

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | Update daily |

  

### 🥉 Read Guidelines

```sql
 SELECT user_id, read_faq granted_at
    FROM user_stats
    WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user is edited or created |

  

### 🥉 Reader

```sql
SELECT id user_id, CURRENT_TIMESTAMP granted_at
    FROM users
    WHERE id IN
    (
      SELECT pt.user_id
      FROM post_timings pt
      JOIN badge_posts b ON b.post_number = pt.post_number AND
                            b.topic_id = pt.topic_id
      JOIN topics t ON t.id = pt.topic_id
      LEFT JOIN user_badges ub ON ub.badge_id = 17 AND ub.user_id = pt.user_id
      WHERE ub.id IS NULL AND t.posts_count > 100
      GROUP BY pt.user_id, pt.topic_id, t.posts_count
      HAVING COUNT(*) >= t.posts_count

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | |

  

### 🥉 Thank You (Likes given + Likes received)

The 🥉 Thank You and 🥈 Gives Back, and 🥇 Empathetic badges follow the same pattern but with different values for `us.likes_given` and `HAVING COUNT(*)`.

```sql
SELECT us.user_id, CURRENT_TIMESTAMP granted_at
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count > 0
        AND us.likes_given >= #{likes_given}
        AND (:backfill OR us.user_id IN (:user_ids))
      GROUP BY us.user_id, us.likes_given
      HAVING COUNT(*) > #{likes_received}

```

| | |
| --- | --- |
| Run revocation query daily | |
| Query targets posts | |
| Trigger | Update daily |

  

### 🥉 Welcome

```sql
SELECT p.user_id, MIN(post_id) post_id, MIN(pa.created_at) granted_at
    FROM post_actions pa
    JOIN badge_posts p on p.id = pa.post_id
    WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
    GROUP BY p.user_id

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user acts on a post |

  

### 🥉 Wiki Editor

```sql
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

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user edits or creates a post |

  

## [Discourse Reactions](https://meta.discourse.org/t/discourse-reactions/183261)

### 🥉 First Reaction

```sql
SELECT user_id, created_at AS granted_at, post_id
  FROM (
           SELECT ru.post_id, ru.user_id, ru.created_at,
                  ROW_NUMBER() OVER (PARTITION BY ru.user_id ORDER BY ru.created_at) AS row_number
           FROM discourse_reactions_reaction_users ru
                JOIN badge_posts p ON ru.post_id = p.id
           WHERE :backfill
              OR ru.post_id IN (:post_ids)
       ) x
  WHERE row_number = 1

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | ✅ |
| Trigger | When a user edits or creates a post |

  

## [Discourse Solved](https://meta.discourse.org/t/discourse-solved/30155)

### 🥉 Solved!

```sql
 SELECT post_id, user_id, created_at AS granted_at
  FROM (
           SELECT p.id AS post_id, p.user_id, dsst.created_at,
              ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY dsst.created_at) AS row_number
           FROM discourse_solved_solved_topics dsst
              JOIN badge_posts p ON dsst.answer_post_id = p.id
              JOIN topics t ON p.topic_id = t.id
           WHERE p.user_id <> t.user_id -- ignore topics solved by OP
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user edits or creates a post |

  

### 🥈 Guidance Counsellor

The badges for 🥈 Guidance Counsellor, 🥇 Know-it-All, and 🥇 Solution Institution all follow the same pattern but with a different `HAVING COUNT (*) >=` threshold:

```sql
SELECT p.user_id, MAX(pcf.created_at) AS granted_at
   FROM post_custom_fields pcf
        JOIN badge_posts p ON pcf.post_id = p.id
        JOIN topics t ON p.topic_id = t.id
   WHERE pcf.name = 'is_accepted_answer'
     AND p.user_id <> t.user_id -- ignore topics solved by OP
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}

```

| | |
| --- | --- |
| Run revocation query daily | ✅ |
| Query targets posts | |
| Trigger | When a user edits or creates a post |

  

## [Discourse Github](https://meta.discourse.org/t/discourse-github/99895)

I can’t read these ones. 🙃

> <https://github.com/discourse/discourse-github/blob/main/app/lib/github_badges.rb>

Sources:

> <https://github.com/discourse/discourse/blob/main/lib/badge_queries.rb>

> <https://github.com/discourse/discourse-reactions/blob/main/db/fixtures/001_badges.rb>

> <https://github.com/discourse/discourse-solved/blob/main/db/fixtures/001_badges.rb>

> <https://github.com/discourse/discourse-github/blob/main/app/lib/github_badges.rb>

---

_[View the full topic](https://meta.discourse.org/t/the-default-badge-queries/276812)._