What cool badge queries have you come up with?

badge

(Daniela) #105

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?


(OMGEDSON) #106

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


(Michael Downey) #107

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…


(Kane York) #108

That’s exactly what you would do :slightly_smiling:


(Kane York) #109

Most commonly used words

by @meglio


(Anton) #110

#111

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


(Jay Pfaffman) #112

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


(Jay Pfaffman) #113

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


#114

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


#115

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

(Jay Pfaffman) #116

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


#117

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


(Jay Pfaffman) #118

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.


(Slevin Kelevra) #119

Hey !

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


(Louis Rossouw) #120

Anyone done a reply by email badge?


(Steven Slade) #121

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!


(Louis Rossouw) #122

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


(Sam Saffron) #123

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


(Louis Rossouw) #124

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