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…

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…

Using that query, we seem to have had a weird issue. It seems to have awarded to staff only, and I’m almost certain some non-staff would meet that criteria. Is this something I broke somewhere, or is it a query issue?

Badge for posts with Likes from a specific group

Community Building Data & reporting

Firepup650 (Firepup Sixfifty) 2023 年 8 月 26 日午後 11:40 29

うーん。それは、例えば、私たちのTL0ロックされたカテゴリをカウントしないということですか？

トピック		返信	表示
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	9812	2023 年 9 月 17 日
Badges for posting Data & reporting sql-triggered-badge	4	499	2024 年 8 月 27 日
Badges for specific category Feature	1	1302	2018 年 6 月 13 日
Some common badge queries Data & reporting sql-triggered-badge	0	8936	2015 年 8 月 7 日
Query to create some groups based on activity Data & reporting sql-query	11	969	2023 年 9 月 5 日

Badge for posts with Likes from a specific group

関連トピック