What cool badge queries have you come up with?

badge

(Michael Downey) #1

NOTE:

So 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!

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…


Example badges?
Best use of the Badge system you have seen
Grant a badge for anyone that posts in a certain category
Wiki Editor Badge
Creating awareness about a forum
Some common badge queries idea
/admin/badges - too many badges?
How do I make my own badge query?
Badge SQL can no longer be edited by default
What are Badges?
Why don't more people use the user_stats table?
Here's how to make a badge for the answer with the most likes in a topic!
Custom Badge rules
SQL query in Data Explorer to pull latest topics?
How to show registered users the ability to add title
How to add SQL Queries to badges?
Badge Discussion: Like Ratios + Spending Likes
When editing badge updated parameters are not visible after saving
Badge Creator Python Script
Why Discourse, and not other community platforms?
New extensibility sub-category: Badge Queries
Are TL3 and TL2 requirements too different?
Badge query request: Received X likes in topic containing specific tag
Show only liked posts in a thread
Will Discourse apply for Google Summer of Code 2015?
A new trust level: The Helpful member?
Badge SQL can no longer be edited by default
Conditions for granting badge
(Michael Downey) #2

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

Thanks @elberet & @Vocino!


(Michael Downey) #3

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


(James Milligan) #4

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


(PJH) #5

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

And

Edit 2: Added :backfill


New badge, grant if post in category AND posted x posts anywhere
(Jeff Atwood) #6

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


Custom consecutive days badge
(Kane York) #7

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.


(PJH) #8

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.


Custom consecutive days badge
(Michael Downey) #9

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


(Kane York) #10
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”.


Custom consecutive days badge
Export a list of people who replied to a specific topic
(Soy) #12

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

Thank you!


(PJH) #13

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.


(PJH) #14

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

Custom consecutive days badge
(Soy) #15

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


(PJH) #16

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.


(Channing Hinton) #17

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


(Erlend Sogge Heggen) #18

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

"Nice Post" threshold is too high for most sites, can we make it easier?
(Alessio Fattorini) #19

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


(PJH) #20

##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)


Badge Discussion: Like Ratios + Spending Likes
Badge Discussion: Like Ratios + Spending Likes
Badge Discussion: Like Ratios + Spending Likes
(PJH) #21
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: