As already mentioned here : Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like. SELECT p.user…

Do you mean you want the same query for all categories? 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 WHERE ( SELECT count(*) FROM post_actions pa WHERE pa.post_id = p.id AND post_action_ty…

That seems like it would work, but seems to fail when the group you’re running it against is staff. I tried both Staff and staff as the group name, and temporarily set post and like count to 1, and it says no badges would be granted. What am I doing wrong here?

hmm i used lower case staff and it worked for me. :thinking: 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 WHERE ( SELECT count(*) FROM post_actions pa WHERE pa.post_id = p.id AND post_acti…

Weird :face_with_spiral_eyes: It still doesn’t work for me. I’ll try running it against a few other groups to see if I can find the issue. Edit: Ran against a different group, and the query still failed. I’m not sure what the issue here would be. Does it depend on primary groups by chance? Edit 2:…

I think I know why. I will work on this for you after I eat some dinner. I need SQL practice anyways. Badge SQL is more restrictive than postgres. I got past the subquery part. :slight_smile:

I’ve not woken up quite yet, and I find badge queries need a solid two cups of tea before being able to tackle them fully, but I’ve been talking to the bot recently about these kind of queries, and I think using the actual post_action_code_id and the group_id code are better than using the nested SE…

I did this to get necessary schema tables for posts, posts_actions, group_users and groups SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<TABLE NAME>'; Then used this to get all the group IDs: SELECT name, id FROM groups ORDER BY name…

I call mine Bert. :slight_smile: Though we have a complicated relationship. I think one other limitation to that type of query is using MIN(p.created_at) granted_at gives me the date of their first one and not, say, the date of their 10th one. It could be changed to MAX, but that would also give a …

yea I agree. something still doesn’t feel right. I’m going to bed. :sweat_smile:

I’m having fun with this though and it’s helping me re-learn SQL and how to write better queries. Having Lola / GPT4bot as a SQL assistant is helpful, but you have to guide her and ask her the right way. I am tried to figure out ways to give her access to most of the schema table information so I d…

Badge for posts with Likes from a specific group

Community Building Data & reporting

Sujet		Réponses	Vues
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	9762	Septembre 17, 2023
Badges for posting Data & reporting sql-triggered-badge	4	473	Août 27, 2024
Badges for specific category Feature	1	1290	Juin 13, 2018
Some common badge queries Data & reporting sql-triggered-badge	0	8889	Août 7, 2015
Query to create some groups based on activity Data & reporting sql-query	11	925	Septembre 5, 2023

Badge for posts with Likes from a specific group

Sujets connexes