The default badge queries

JammyDodger · August 27, 2023, 9:09am

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)

 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(*).

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

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.

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

(Tracking it down)

Editor

 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.

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

(Tracking it down)

First Flag

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 (3,4,8) 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

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

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

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

(Tracking it down)

First Quote

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

(Tracking it down)

Original community suggestion 'Reply by email' badge - #3 by lrossouw

First Share

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

(Tracking it down)

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.

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.

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(*) >= #{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.

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.

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

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.

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

 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

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(*).

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

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

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

First Reaction

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

Solved!

SELECT post_id, user_id, created_at AS granted_at
  FROM (
           SELECT p.id AS post_id, p.user_id, pcf.created_at,
                  ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY pcf.created_at) AS row_number
           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))
       ) 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:

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

I can’t read these ones.

github.com

discourse/discourse-github/blob/main/app/lib/github_badges.rb

# frozen_string_literal: true

module DiscourseGithubPlugin
  module GithubBadges
    BADGE_NAME_BRONZE ||= "Contributor"
    BADGE_NAME_SILVER ||= "Great Contributor"
    BADGE_NAME_GOLD ||= "Amazing Contributor"

    COMMITTER_BADGE_NAME_BRONZE ||= "Committer"
    COMMITTER_BADGE_NAME_SILVER ||= "Frequent Committer"
    COMMITTER_BADGE_NAME_GOLD ||= "Amazing Committer"

    class Granter
      def initialize(emails)
        @emails = emails
        @badges = []
      end

      def add_badge(badge, as_title:, threshold:)
        @badges << [badge, as_title, threshold]

This file has been truncated. show original

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

Lilly · August 27, 2023, 2:08pm

This is awesome @JammyDodger thank for doing this helpful topic!

Topic		Replies	Views
Badge DB seed is broken bug	8	713	January 27, 2019
Fail to upgrade discourse support	5	1821	December 3, 2016
What is the bug reporter badge? data & reporting sql-triggered-badge	33	9144	September 17, 2023
Badge Discussion: Like Ratios + Spending Likes feature badges	70	11598	April 9, 2016
Badges based on User Custom Fields? data & reporting sql-triggered-badge , user-custom-fields	21	169	November 13, 2020

The default badge queries

Core Badges

Anniversary

Appreciated (number of Likes on multiple posts)

Autobiographer

Basic (Trust Levels)

Certified and Licensed

Editor

Enthusiast

First Emoji

First Flag

First Like

First Link

First Mention

First Onebox

First Quote

First Reply-by-Email

First Share

New User of the Month

Nice Reply (Likes on a Post)

Nice Share (Link Sharing)

Nice Topic (Likes on a Topic)

Out of Love (Max Likes in a day)

Popular Link (Link Clicks)

Promoter (Invites)

Read Guidelines

Reader

Thank You (Likes given + Likes received)

Welcome

Wiki Editor

Discourse Reactions

First Reaction

Discourse Solved

Solved!

Guidance Counsellor

Discourse Github

Related Topics