# Make a badge for the answer with the most likes in a topic!
**URL:** https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [08.Декабрь.2015 20:12:52 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440 "2015-12-08T20:12:52Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@stevenpslade](https://meta.discourse.org/u/stevenpslade)
#### Post date: [08.Декабрь.2015 20:12:52 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440/1 "2015-12-08T20:12:52Z")
Continuing the discussion from [What cool badge queries have you come up with?](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978):
> [@\[Superseded\] What cool badge queries have you come up with?](https://meta.discourse.org/t/superseded-what-cool-badge-queries-have-you-come-up-with/18978/1):
>
> > 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](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/5)
>
> - [Replying to a specified topic](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/10)
>
> - [Reading a specified number of posts](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/20)
>
> - [X likes over Y days](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/21)
>
> - [N likes across all posts in a single topic](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/22)
>
> - [Filling out a custom user field](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/26)
>
> - [Top X% poster in the previous month](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/35)
>
> - [at least X topics with Y likes from group Z in a specific category](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/82)
>
> - [having mailing list mode on](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978/83)
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;
```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [25.Март.2016 20:55:07 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440/2 "2016-03-25T20:55:07Z")
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](https://meta.discourse.org/t/32566?silent=true) because it isn’t a table
```plaintext
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])));
```
---
### Author:  [@stevenpslade](https://meta.discourse.org/u/stevenpslade)
#### Post date: [28.Март.2016 17:04:41 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440/3 "2016-03-28T17:04:41Z")
Thanks for letting me know! Good tip on the badge\_posts view. 👌
---
### Author:  [@jesselperry](https://meta.discourse.org/u/jesselperry)
#### Post date: [29.Март.2016 22:00:46 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440/4 "2016-03-29T22:00:46Z")
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.
---
### Author:  [@stevenpslade](https://meta.discourse.org/u/stevenpslade)
#### Post date: [29.Март.2016 22:39:29 UTC](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440/5 "2016-03-29T22:39:29Z")
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. 🤑