Continuing the discussion from What cool badge queries have you come up with?:
I’m trying to figure out the following. I want the SQL query below to work in a single category ID:
SELECT id user_id, current_timestamp granted_at FROM users WHERE id IN ( SELECT p1.user_id FROM post_custom_fields pc JOIN badge_posts p1 ON p1.id = pc.post_id JOIN topics t1 ON p1.topic_id = t1.id WHERE p1.user_id <> t1.user_id AND name = 'is_accepted_answer' AND p1.user_id IN ( SELECT user_id FROM posts WHERE :backfill OR p1.id IN (:post_ids) ) GROUP BY p1.user_id HAVING COUNT(*) > 9 )
Now it checks the global forum, but I want to merge it with a query like this:
SELECT DISTINCT ON (t.user_id) t.user_id, t.created_at granted_at FROM topics t WHERE t.category_id = 28
Any suggestions? Sorry, SQL has been a while for me.