What cool badge queries have you come up with?

Don’t remember who share SQL for “top poster”, sorry. This is the code shared somewhere here on Meta (I saved it in a topic on my forum)
Take a look here:

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 */

Saw your code, may I ask if do you want a ‘top poster’ badge only for TL3?

Sorry to clarify, i’m really really new at this, I just want to know what code I should paste to show 1 member with the most new site-wide posts of the month. As a bonus I’d love to EXCLUDE members of a certain trust level.

I’d greatly appreciate any help.

-e

I think you’d just add a line with LIMIT 1 at the end of something like the Top X% posters query above. Maybe I’m missing something though…

That’s exactly what you would do :slightly_smiling:

Most commonly used words

by @meglio

1 Like
1 Like

Hi Guys,
I have been reading through and been looking for two in particular

  1. One to check if you have posted a topic in a particular category - like introductions
  2. One to check if you have filled in particular user_fields such as location, about me and added profile picture etc.

Thanks,
Mike

This matches if a post with a particular tag has been posted in a particular category. You can remove the AND tcf.name ... line 3 from the bottom if you don’t care about tags.

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN topic_custom_fields tcf on t.id = tcf.topic_id
WHERE category_id = ANY (
  SELECT id FROM categories WHERE name ilike 'CATEGORY-NAME'
) AND p.post_number = 1
AND tcf.name LIKE 'tags' and tcf.value LIKE 'TAG-NAME'
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

3 Likes

I made a script to create sets of badges like this:

1 Like

I am sure I am going wrong here somewhere - This is what I am trying to use:

SELECT user, current_timestamp granted_at
FROM user_profiles
WHERE location IS NOT NULL

However is only returning

current_user granted_at
discourse 2016-03-08 12:40:44.57532+00

Which is certainly not correct as discourse is not a member name :slight_smile: and should be returning my account name

EDIT: Found the issue I needed to use user_id rather than user

Need some feedback please - would it be better to run the following 3 queries seperately to create badges and then check those badges have been granted to grant a seperate badge again. Or could someone please assist me in merging these 3 queries into 1 in order to have it grant a badge if each part of it has been done.

Thanks,

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts bp
INNER JOIN user_profiles up
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 5

SELECT user_id, current_timestamp granted_at
FROM user_profiles
WHERE location IS NOT NULL

SELECT user_id, current_timestamp granted_at
FROM user_profiles
WHERE bio_raw IS NOT NULL

I can’t quite figure out the first query. Can you give it in English? Is that supposed to be “made 5 posts?”

The second two:

Users with a BIO and LOCATION

SELECT user_id, current_timestamp granted_at
FROM user_profiles up
WHERE location IS NOT NULL AND
bio_raw IS NOT NULL
AND    (:backfill OR up.user_id IN (:user_ids) )

This version, based on the Autobiographer badge with the location requirement added might be a bit better, though, as it checks that the bio is a certain lenght (remove the avatar requirement if you like):

  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
          up.location IS NOT NULL AND
          (:backfill OR u.id IN (:user_ids) )

But I think this was the easy part. . .

3 Likes

Yeah the first one was to check that 5 posts have been made - someone posted it up earlier but was related to 1000 posts which I just modified. Thank you for providing the last one that’s exactly what I was after. It doesn’t include gravatar however does it so I can just add that in.

What does the :backfill do in the queries?

Appreciate your help @pfaffman

Got it. Yeah. I tried pasting those together and couldn’t quite get it.

I can’t explain in a cogent way. I think that you need it for any query that targets posts.

Hey !

How do you get a badge granted when a user do the konami code ?

1 Like

Anyone done a reply by email badge?

1 Like

I’m not too sure, but it shouldn’t be too hard considering the Post table has via_email boolean. Find Posts where via_email is true and get the user_id of that post!

1 Like

Here is a Reply by Email Badge:

select
    p.user_id
    ,min(p.created_at) granted_at
    ,min(p.id) post_id
from badge_posts p
where
    (:backfill or p.id IN (:post_ids) ) 
    and p.post_number >1
    and p.via_email=True
group by 
    p.user_id

7 Likes

That is a great idea for a badge, we should add it for our next release (adding it to the list)

7 Likes

Could also do a topic created via email badge (but I haven’t activated that functionality).
Probably something like:

select
    p.user_id
    ,min(p.created_at) granted_at
    ,min(p.id) post_id
from badge_posts p
where
    (:backfill or p.id IN (:post_ids) ) 
    and p.post_number =1
    and p.via_email=True
group by 
    p.user_id
1 Like