Continuing the discussion from What cool badge queries have you come up with?: [image][Superseded] What cool badge queries have you come up with? So there are lots of potential badges that we can come up with using SQL queries. I thought I’d start a topic for people to share some of the queries and badges they’ve created. I for one am looking forward to seeing some cool inspiration! This post is a wiki topic. If you have a good badge query to add, please reply to this topic with your query, and then edit this post (pencil icon in the upper right) and include a link to your reply here in the following index. Thanks! Badges for… 1000 (or any number of) posts Replying to a specified topic Reading a specified number of posts X likes over Y days N likes across all posts in a single topic Filling out a custom user field Top X% poster in the previous month at least X topics with Y likes from group Z in a specific category having mailing list mode on 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;

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])));

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.

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:

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

Community Building Data & reporting

stevenpslade (Steven Slade) March 28, 2016, 5:04pm 3

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

Topic		Replies	Views
What query to award a badge for liking or replying to a particular topic? Data & reporting sql-triggered-badge	1	691	May 23, 2023
Likes in a short time/in one topic Data & reporting sql-triggered-badge	2	211	February 15, 2015
Badge for popular topic Data & reporting sql-triggered-badge	5	489	June 1, 2024
Request for badge: Nice/Good/Great Topic Feature badges	3	1191	September 11, 2014
Badge ideas for Topic Voting Feature badges , topic-voting , sql-triggered-badge	0	1912	June 24, 2016

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

Related topics