What cool badge queries have you come up with?

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.

3 Likes

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:

6 Likes

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.

1 Like

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

How can you have obtained this?

3 Likes

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
16 Likes

We’d like to have a badge granted to our staff members that have read the guidelines placed in our hidden staff category.

This would (presumably) be very similar to the “Read Guidelines” badge:

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

With a modification to allow us to specify specific topics or post, and add more in the future.

Could anyone help with that?

1 Like

Thanks so much for everyone contributing their knowledge and experience on this thread, so that those of us who are new to Discourse can make the most of this amazing platform!

4 Likes

Unlikely because read_faq is ‘special’ and not reprogrammable or extendable. The following may do what you’re after however:

SELECT user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE topic_id = /* put the topic id of your guidelines here */
AND post_number = /* this will probably be 1 since I suspect it's the only thing there */
AND msecs > 10000 /* they've looked at it for more than 10 seconds */
10 Likes

Thanks very much @PJH!

1 Like

Test it first… I’m not sure how/when the msecs column gets updated.

I’d also recommend running it -at most- daily, with no revocation.

1 Like

Works, thanks! Though this probably isn’t the ideal solution, n the interest of giving back to the community, here’s our multipost version.

    SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at    
FROM post_timings
JOIN (
     SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at      
     FROM post_timings
     JOIN (
           SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at
           FROM post_timings
           WHERE topic_id = 206 
           AND post_number = 1
           AND msecs > 10000 
          ) a ON a.user_id = post_timings.user_id
     WHERE topic_id = 1066
     AND post_number = 1
     AND msecs > 10000
    ) b ON b.user_id = post_timings.user_id
WHERE topic_id = 1043
AND post_number = 1
AND msecs > 10000

How about:

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE
( topic_id = 206 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1066 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1043 AND post_number = 1 AND msecs > 10000)
GROUP BY user_id
HAVING count(*) = 3

Edit: Changed since it was pointed out to me that the original wouldn’t have worked.

3 Likes

Regular Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 3

Description: Users with this badge have been a regular user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 3.

Leader Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 4

Description: Users with this badge have been an exemplary user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 4.


Revocation query: NO (This is the important one :slight_smile: )
Targets posts: NO
Trigger: Daily
As a title: YES
Granted multiple: NO
Show badge: YES
Show post granting badge: NO
Enable: YES

8 Likes

Couldn’t this be rewritten as:

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND ( topic_id = 206 OR topic_id = 1066 OR topic_id = 1043 )
GROUP BY user_id
HAVING count(*) = 3

I’m no SQL expert, just seems like it should be possible given the duplication in the original query.

3 Likes

Presuming 10 seconds is sufficient for all the posts, and the first post in each topic is the one being targeted, yes…

I don’t believe that sort of data is retained or accessible from the badge queries.

2 Likes

Yeah, not sure what a good read time is, but 10 seconds seems okay.

I would also assume the first post is the focus.

Also couldn’t this be simplified a bit more? (Again naive SQL assumptions)

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND topic_id IN ( 206, 1066, 1043 )
GROUP BY user_id
HAVING count(*) = 3
4 Likes

All are pretty great, though this one strikes me as the most extensible.

But this is just code golf now, right?

4 Likes

I had the same thought myself…

1 Like