What cool badge queries have you come up with?

:warning: Enabling Badge SQL entails security and performance risks so it is not available by default.
For more information, see: Enable Badge SQL

There are lots of potential badges that we can come up with using SQL queries. I thought I’d start a topic for people to share some of the queries and badges they’ve created. I for one am looking forward to seeing some cool inspiration!

:information_source: This post is a wiki topic. If you have a good badge query to add, please reply to this topic with your query, and then edit this post (pencil icon in the upper right) and include a link to your reply here in the following index. Thanks!

Badges for…


More Ideas : Some common badge queries

74 Likes

The first one isn’t my own but one that inspired me to start this list:

https://meta.discourse.org/t/how-can-i-grant-a-badge-to-the-first-500-users-of-my-forum/18955?u=downey

Thanks @elberet & @Vocino!

9 Likes

Here’s another cool one I found from @sam:

5 Likes

Sure to be a standard one for many people, @sam’s group membership badge query:

5 Likes

Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.

Generic version:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 1000

Change the 1000 for whatever post limit you’re after. I’ve themed ours (due to the nature of our board) on powers of 2.

Note that this will (should) only count posts that are public (not in categories that are restricted or private messages) or otherwise unrestricted (checkbox on edit category ignoring badges)

Additionally, for our board I’ve had to exclude certain topics due to the nature of my charges:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE topic_id NOT IN (
   [elided]
) AND topic_id NOT IN (
   [elided]
) AND  (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) >= 2048

Edit: There was a problem with my queries - post_id is apparently required when this is triggered on post-related triggers.
See
https://meta.discourse.org/t/badges-sql-problem/19295

And

Edit 2: Added :backfill

14 Likes

I moved 11 posts to a new topic: Custom consecutive days badge

I think I have the correct trigger condition here, actually:

    :backfill OR
    user_id IN (
        SELECT trigger_post.user_id FROM posts trigger_post WHERE trigger_post.id IN (:post_ids)
    )

You get the user id list from the list of triggered posts and filter on that.

4 Likes

I’ve changed the query to be less DB intensive (only needs to run once per day), time insensitive (doesn’t matter when it’s run) and (unfortunately) much longer. Here if anyone’s interested.

2 Likes

Anyone want a (hopefully easy) challenge? A badge granted when someone posts a reply in a single specified topic. (i.e., A “Please introduce yourself here” topic.)

9 Likes
SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 32 AND -- 32 is the "please introduce yourself" topic
  (:backfill OR p.id IN (:post_ids) )

Triggered on “edit/create a post”.

16 Likes

Can someone please help me with the SQL and everything if I wanted to do one for “First post” badge!

Thank you!

1 Like

Allow a ‘manually’ awarded badge to be associated with someone’s post by merely replying to that post and including some set-text in the reply.

The badge goes to the post being replied to automatically without the need to go to /admin/users/USER/badges and awarding it there.

https://meta.discourse.org/t/badges-request-for-reason-field-on-manual-badge-assignment/17625/7?u=pjh

5 Likes

You mean the first public post that they ever make to your forum?

  1. /admin/badges
  2. + New
  3. Give your badge a name
  4. Pick an icon. The font-awesome names can be found here (fa-pencil-square for example) or use a hosted image. This will be used on the various badges pages dotted around your site.
  5. Pick an (optional) image. You can use the same as the previous step. This image will appear on user cards.
  6. Select a badge type. For this you’d probably want Bronze.
  7. Pick a group (or create a new group for it first.)
  8. Give your badge a description
  9. Enter the following sql
SELECT MIN(bp.id) post_id, bp.user_id, MIN(bp.created_at) granted_at
FROM badge_posts bp
WHERE (:backfill OR bp.id IN (:post_ids) )
GROUP BY bp.user_id
  1. You probably want the following options checked:
  2. Save your badge and refresh the page to get more options between the SQL and the options shown above and you probably want the following
15 Likes

Wah this is perfect! Thanks PJH! Are there any resources I could look at to create more badges?

1 Like

Not that I’ve found - everything I’ve done has been through trial and error, what passes for SQL that I’ve picked up over the years and mucking around with an offline restored-backup of the database in phppgadmin. And a bit of guesswork.

As a result I’ve created quite a few badges with fairly hairy queries on our site -

  • “Made X posts. But not in certain topics”
  • “Spent X consecutive days on the forum”
  • “Top X% poster in the last month. Except certain topics”
  • The above one to allow easier awarding of ‘manual’ badges.

Currently contemplating “Has had a post replied to X times” but that one’s proving problematic depending on how you query the database.

4 Likes

How about a badge awarded to posts that contain attachments? What sql query would accomplish that?

I run a game development community and showcasing your game is always a great way to harvest Likes. As far as grand total of Likes go, spreading your updates out is probably the way to go, but the Badge system won’t take much notice of you unless you save up for bigger updates (especially as long as the default threshold is 10).

Here’s a guy who’s getting a decent amount of attention for his good work, but no badges.

What about a default badge with a query along these lines:

Likeable Poster:

30 likes accumulated over the span of 15 days

Could be one-time-award to keep things simple. Else one would have to do a reset of the count once it’s awarded (while possibly still counting on the old “score” as well in case there’s a 60-likes badge as well).

Bonus idea:

Topic Participant Extraordinaire

total of 30 likes spread over posts in the same topic
4 Likes

I read this post just today for the first time. Fantastic repo of custom badges for increase engaging and encourage gamification

1 Like

##Badges for reading!

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
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

I highly recommend not running this query more frequently than daily - our forum’s only been running for 10 months and the table referenced here has 11,949,028 rows making this query rather time intensive (6 seconds per badge - most of our badge queries are sub-second.)

And yes - as it stands - PMs, flags and non-public posts are counted in this - trying to exclude those pushes the execution time of the query even higher… (ref 1, ref 2)

14 Likes
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id /* Get post details */
INNER JOIN users r on r.id=p.user_id /* The user who made the post that was liked */
WHERE pa.post_action_type_id=2 /* The user who liked the post */
AND pa.created_at > CURRENT_DATE - INTERVAL '15 DAYS' /* Change to suit */
GROUP BY Liked, r.id
HAVING count(*) > 30 /* Change to suit */
ORDER BY count(*) DESC

I’d recommend running that once a day (certainly not every post edit/creation) - it takes 2 seconds to run on my backup instance, though I’m sure the query could be tweaked to make it quicker.

In which case you’ll want:

3 Likes