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_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
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?
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_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE 'staff' )
)
) >= 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1
Weird 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: That wouldn’t work, staff doesn’t seem to be allowed to be set as a primary group.
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.
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 SELECT queries to find the same thing.
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
So then I included all the schema tables needed and instructed Lola, er GPTbot to use actual post_action_code id and the group_id code. Then after some arguing back and forth and making some corrections. We came up with this. Again, seems to work in Data Explorer, but I still cannot get anything out of it in the Badge Previewer.
G = group_id
X = minimum number of likes
Y = minimum number of posts
SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X
I call mine Bert. 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 ‘not right’ date if you ran it against historical data where they already had more than 10.
I’m still mulling that one over.
I had some success using ROW_NUMBER(), but nothing concrete as yet.
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 don’t have to do it for every query problem we work on. Feeding the table schema info gives much better results. I tried giving her a link to the available schemas in core but that just made her go fart around on google.
I am interested to work with her when I know the badge query previewer is working. I need to practice SQL and doing badge queries. Incidentally she cannot fix it and she still makes my earl tea grey not hot enough. Although last night’s SQL lesson was the best date I’ve had in years.
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?
After a confusing blip with some deleted Likes throwing my tests off, I think this is a revised version of the one in the OP that fits your criteria:
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = 2
AND deleted_at IS NULL
AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
) >= 5
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
It works off the badge_posts view so it only counts posts from public categories, which you may want to take into consideration depending on your forum/category set up. Also, using CURRENT_TIMESTAMP for granted_at is another option, but is probably down to a matter of taste.
I’m trying to figure out if a CTE is more efficient, but it’s melting my brain
WITH staff_liked AS (
SELECT COUNT(*), pa.post_id
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE post_action_type_id = 2
AND gu.group_id = 3
AND deleted_at IS NULL
GROUP BY pa.post_id
HAVING COUNT(*) >= 5
)
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE p.id IN (SELECT post_id FROM staff_liked)
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
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.
well done. 
