Continuing the discussion from What cool badge queries have you come up with?:
I wanted to give a badge if a user’s answer had received the most likes in a topic. Here’s what I came up with:
SELECT topics.id AS topic, posts.id AS post, users.id AS user_id, users.title, MAX(posts.like_count) AS like_count, CURRENT_TIMESTAMP granted_at
FROM topics
JOIN posts ON topics.id = posts.topic_id
JOIN users ON users.id = posts.user_id
WHERE posts.like_count > 0
AND (:backfill OR pa.post_id in :post_ids)
GROUP BY topics.id, posts.id, users.id, users.title
HAVING MAX(posts.like_count) >= (
SELECT MAX(p.like_count)
FROM topics t
JOIN posts p ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
WHERE t.id = topics.id
GROUP BY u.id ORDER BY max desc LIMIT 1)
ORDER BY MAX(posts.like_count) DESC;