Make a badge for the answer with the most likes in a topic!

data & reporting
1

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;
10 Likes
2

I’m not sure if you’re interested in modifying the query, but from my few tests on localhost I’ve found using the badge_posts VIEW ran faster than using the posts table.
* it doesn’t show in the Data Explorer because it isn’t a table

CREATE VIEW badge_posts AS
 SELECT p.id,
    p.user_id,
    p.topic_id,
    p.post_number,
    p.raw,
    p.cooked,
    p.created_at,
    p.updated_at,
    p.reply_to_post_number,
    p.reply_count,
    p.quote_count,
    p.deleted_at,
    p.off_topic_count,
    p.like_count,
    p.incoming_link_count,
    p.bookmark_count,
    p.avg_time,
    p.score,
    p.reads,
    p.post_type,
    p.vote_count,
    p.sort_order,
    p.last_editor_id,
    p.hidden,
    p.hidden_reason_id,
    p.notify_moderators_count,
    p.spam_count,
    p.illegal_count,
    p.inappropriate_count,
    p.last_version_at,
    p.user_deleted,
    p.reply_to_user_id,
    p.percent_rank,
    p.notify_user_count,
    p.like_score,
    p.deleted_by_id,
    p.edit_reason,
    p.word_count,
    p.version,
    p.cook_method,
    p.wiki,
    p.baked_at,
    p.baked_version,
    p.hidden_at,
    p.self_edits,
    p.reply_quoted,
    p.via_email,
    p.raw_email,
    p.public_version,
    p.action_code
   FROM ((posts p
     JOIN topics t ON ((t.id = p.topic_id)))
     JOIN categories c ON ((c.id = t.category_id)))
  WHERE (((((c.allow_badges AND (p.deleted_at IS NULL)) AND (t.deleted_at IS NULL)) AND (NOT c.read_restricted)) AND t.visible) AND (p.post_type = ANY (ARRAY[1, 2, 3])));
3 Likes
3

Thanks for letting me know! Good tip on the badge_posts view. :ok_hand:

4

When would this badge be awarded? Is there a time limit after the topic is created before it considers who got the most likes? I wouldn’t want it to award to someone that got one like on the first post in a topic.

5

I believe with Run revocation query daily enabled then this badge could be rewarded and redacted accordingly. So if someone has the most like one day, it could change the next. I supposed you could do a time limit with post.created_at or another option could be to wait until X amount of likes have been given in a topic.

Currently though, this query does not judge. If a person has 1 like, and that’s the most, congratulations to them! Kind of promotes a bit of competition in my mind. If someone only has 1 like, maybe it’s the perfect post for me to swoop in and get 2 likes. :money_mouth: