# 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:** [2015 年 12 月 8 日午後 8:12 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:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![stevenpslade](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stevenpslade/32/53550_2.png) [@stevenpslade](https://meta.discourse.org/u/stevenpslade)\
**Post date:** [2015 年 12 月 8 日午後 8:12 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")

</div>

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;

```

---

_[View the full topic](https://meta.discourse.org/t/make-a-badge-for-the-answer-with-the-most-likes-in-a-topic/36440)._