What cool badge queries have you come up with?

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:


(PJH) #22
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at, p.topic_id
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id
INNER JOIN users r on r.id=p.user_id
WHERE pa.post_action_type_id=2 
GROUP BY Liked, r.id, p.topic_id
HAVING count(*) > 30
ORDER BY count(*) DESC

10 seconds. Certainly no more frequently than once a day.


(PJH) #23

Depends on what about those posts you’re after. Basically a post with an attachment is generally one with a picture in, so something like:

SELECT user_id
FROM posts
WHERE raw ilike '%<img src="%"'

would probably be a good start.


(rddddddd) #24

Im planning to open a forum where there could be some parallel trade and promotion of it in other platforms.
Are there any kinf of “trust” badges? Like pro trader, or reliable vendor…
If there are not, maybe i could open a category for trading profiles, where anyone would have a selfthread where people could leave feedback, and give likes to the OP presentation post, and give badges if likes for that post > x.


(PJH) #25

You can create your own and award them manually.

Or you could probably use groups for this (/admin/groups/custom) which - at a guess - would be more useful for what you’re after.

Groups would allow you to create a category where group members could create topics, yet still allow non-group members to reply to existing topics (but not create their own.) Is that the sort of thing you’re thinking of?


(Michael Downey) #26

Has anyone come up with any badges based on some value for custom user fields? If so, how can we get at those custom user fields with a SQL query?

Here’s a sample badge (there is surely better SQL to do this!) that we quickly hacked together to look for people who filled out an optional custom user field, in this example, to list their GitHub User ID:

    SELECT cf.user_id user_id, cf.updated_at granted_at
    FROM user_custom_fields cf
    WHERE cf.name like 'user_field_2' AND
    LENGTH(cf.value) > 1

Note that the value of name field is actually user_field_1, user_field_2, etc., and not the name that you’ve assigned in the admin section.


(Lisa Wess) #28

Awesome badge. Thank you for sharing this!


(Mykhailo Poliarush) #29

Hi everyone,

I don’t know too much about database structure to create badge sql. I would like to create following badges which is not present in discourse (i think not only i interested in such badges). Please help me out to create following badges:

  • user that made more than 100 comments in general
  • user that made more than 100 comments in particular category
  • user that made more than 100 comments with given at least 5 likes
  • user that gave ‘likes’ more than 100 times in general
  • user that received more than 100 ‘likes’ in general
  • user that received more than 100 ‘likes’ in particular category
  • user that created more than 50 topics in general
  • user that created more than 50 topics in particular category
  • badge for 5 most active users in particular category for a month
  • badge for 5 most active users in general for a month
  • badge for 5 most active users for particular trust level
  • user that shared github gist and received like on comment
  • user that wrote some code blocks in comment and received like on comment

I would gladly appreciate any help on any mentioned items. Many thanks,


(Tobias Eigen) #30

I :heartpulse: this - thanks for sharing! Works for me.

One thing I noticed: it’s not immediately apparent on the custom user field which user_field_n goes which which user field and it doesn’t start at 1! I found it by looking at the source of that page.

Do you know how to modify this query to display it only when a user has another badge already? I’d like to display a “Tech Ambassador” badge on my site to people but only to those users who have self-identified themselves as such and have also provided a bio.


(Michael Downey) #31

I think you’d simply add the following:

AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE cf.user_id = ib.user_id
  AND ib.badge_id = 888
)

Where 888 is the badge ID number of the other prerequisite badge. See the following for details:


(PJH) #32

Well the name given should be in a table somewhere, though joining on it to create a dynamically generated field name may not make the query more self documenting (if that’s even possible in postgres)…

And you’ll have to be careful when editing (specifically adding and removing other) fields of course.


(Michael Downey) #33

Agreed. I’m hopeful to find an answer related to that here:


(Alessio Fattorini) #34

How can you have obtained this?


(PJH) #35

Sure I’d already posted this query, but… this is the one for the top 10%-25% (I have comparable queries for 5%-10%(silver), and top 5%(gold) and top poster (again gold but CSS’d to be a brown spoon)).

Here’s a short version that most sites could probably use:

WITH LastMonth AS ( /* Count eligible posts from last month */
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id 
    WHERE bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS ( /* Total relevant users that have posted in the last month */
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.10 AND /* 10% - change this*/
    row_number < TotalUsers.max *.25 /* 25% - change this */


Our site has people who like gaming the system so, so our actual query (presented below) has a few extra clauses that

  • Exclude particular topics (over and above those excluded normally from badge_posts) such as topics created solely for the purpose of encouraging excessive posting
  • Excludes particular ‘users’ (actually 'bots - they don’t get this badge)
  • Excludes particularly short topics:
WITH exclusions AS ( /* Which other topics to exclude from counters */
    SELECT user_id, id, topic_id, post_number
    FROM posts
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
),
LastMonth AS ( /* Count eligible posts from last month */
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id AND
        bp.user_id NOT IN ( /* ignore bots */
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                    SELECT g.id
                    FROM groups g
                    WHERE g.name IN ('bots')
                )
        )
    WHERE topic_id NOT IN ( /* exclude short topics */
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10
    ) AND topic_id NOT IN ( /* Ineligible topics */
       SELECT topic_id
       FROM exclusions
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.1 AND
    row_number < TotalUsers.max *.25

Special "Leaderboards" for Discourse Forums?