What cool badge queries have you come up with?

badge

(Steve) #84

I’m looking for something similar, a badge awarded when replying to posts in a particular category.


(Kane York) #85
SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at
FROM badge_posts p
JOIN topics t ON p.topic_id = t.id
WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions')
GROUP BY p.user_id

(Steve) #86

Crikey, if this is what I think it is, how easy is it to adapt to specify the number of posts for silver/gold badges?

SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at
FROM badge_posts p
JOIN topics t ON p.topic_id = t.id
WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions')
GROUP BY p.user_id
HAVING COUNT (*) > 10

Would it be that simple?

Turns out it is. Awesome, thanks @riking!

Update: throws an error on any category name which isn’t unique.


(PJH) #87

Try:

instead?


ObOT: I note that <ins> has been CSS’d on here, but not <del>


(Kane York) #88

It’s just terrible contrast vs the quote.


(Ionuț Staicu) #89

Hey guys, there are any chance to have a badge for a suspended user? (and keep that badge after the suspended period expired)

Thanks!


(Sam Saffron) #90

“Mark of Cain” ?

I would strongly recommend against such a badge and in fact not work on writing it for you, but it is very doable.

Badges are meant to be a positive thing, if you start using them for negative stuff you send some really weird messages out there


(Ionuț Staicu) #91

That’s true. The thing is that we have a community where, in more than a year we have exactly two suspended users. So they are in very exclusive club, and when we joked about this, this idea came up: why we don’t give them a badge? :smile:


(PJH) #92

If it’s that rare, and if you really want to do it, simply assign them manually at /admin/users/USERNAME/badges.

I’ll attest to the fact that ‘negative’ badges aren’t really a good idea on most forums, having created a few myself and seen the fallout of one of them…


(Mittineague) #93

@iamntz

So you would have no problem if you were awarded a “made an unpopular suggestion” badge here at meta?


(Steve) #94

I vote it’s called ‘king of bad ideas’, can be awarded more than once, and I get at least two.


(Ionuț Staicu) #95

@Mittineague: As long is a very, very limited badge? YES!

@pjh: That is enough, Thanks!


(Christopher Heald) #97

If you want your query to not be dependent on system field labels such as user_field_2, you can do the following:

    SELECT cf.user_id user_id, cf.updated_at granted_at
    FROM user_custom_fields cf
    INNER JOIN user_fields uf
      ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer
      AND uf.name = 'FriendlyFieldName'
    WHERE LENGTH(cf.value) > 0

It seems like you’d want to check for a length value greater than zero, just in case you have some single-digit UID GitHub graybeards. And if you are granting a badge multiple times, you’ll want to add GROUP BY cf.user_id to be safe.

We have a dev, a staging, and a live site, and the custom user fields we manually add don’t always have the same user_field_n values from site to site.


#98

Hello!

I want to create a badge for those who help in the forums, so I want to give it to the people who have replied X times to ANY thread inside the category ‘Help’. Of course this has to avoid replies in your own topic.

Please any help would be great! I have no idea how to deal with this kind of databases (I’m new to discourse :smile:)


(Daniela) #99

I need something similar. For now I use this code, trigger daily:

And this is the problem:

Users that start topics inside particular category must be excluded from count.
We have a little number of users that write a lot of posts in a single topic only to reply a single question :frowning:


(Alessio Fattorini) #100

I’m interested into it as well, how to avoid such problem?


(OMGEDSON) #104

Hi ! Sorry if my question is silly but just to verify, is this how to highlight the top poster of the month? I really need that functionality on our site to reward the top poster with a sub to our channel… Thank you!!

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 3FROM LastMonth, TotalUsers
WHERE row_number = 1

-e, n00b


(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…