# אילו תגובות הכי נפוצות בקהילה שלכם?
**URL:** https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866
**Category:** Community Building
**Tags:** reactions, sql-query
**Created:** [28 בנובמבר, 2023, 5:43pm UTC](https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866 "2023-11-28T17:43:50Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@jericson](https://meta.discourse.org/u/jericson)
#### Post date: [28 בנובמבר, 2023, 5:43pm UTC](https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866/1 "2023-11-28T17:43:50Z")
Continuing the discussion from [Reactions on Meta](https://meta.discourse.org/t/reactions-on-meta/265377/21):
I was curious about how reactions are used on [a site I work with](https://talk.collegeconfidential.com) part-time. It’s a forum about college admissions, so there are posts about students being accepted/rejected from their dream school and that sort of thing. We use 👍 as our “like” emoji, so that’s why it ranks so high compared to ❤, which is the default “like”. Here’s what I found:
| reaction | posts | count |
| --- | --- | --- |
| 👍 | 260494 | 720859 |
| 💯 | 16116 | 25738 |
| ❤ | 13315 | 23523 |
| 🎉 | 7538 | 22674 |
| 🤣 | 7200 | 15720 |
| ❤️🩹 | 6341 | 26875 |
| 💡 | 3854 | 4404 |
| 😲 | 2795 | 4911 |
| 😄 | 2286 | 2881 |
| 😂 | 1758 | 2623 |
| 😭 | 1708 | 2360 |
| 😠 | 1613 | 3396 |
| 😉 | 336 | 352 |
| ✔ | 30 | 43 |
I created [the initial list of reactions](https://talk.collegeconfidential.com/t/adding-some-emoji-reactions/3624211) by looking at [which are the most commonly-used emojis in posts](https://jlericson.com/2022/12/16/find_emojis.html). We adjusted the list based on [member feedback](https://talk.collegeconfidential.com/t/emoji-addition-change/3626877). I’m a fan of ✔ but it was clear very early on that the community wasn’t. So I swapped it for something else.
If you want to see how your site compares, the query I used is:
```plaintext
select ':'||reaction_value||':' reaction,
count(*) posts,
coalesce(sum(reaction_users_count), sum(like_count)) count
from discourse_reactions_reactions drr
join posts p on post_id = p.id
group by reaction_value
order by count(*) desc
```
**Fun fact:** we struggled to figure out why the 🎁 emoji was so commonly used in posts. Eventually [I tracked down the culprit](https://talk.collegeconfidential.com/t/adding-some-emoji-reactions/3624211/11?u=cc_jon): 🤖.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [11 בדצמבר, 2023, 10:46am UTC](https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866/2 "2023-12-11T10:46:07Z")
Let’s slide this over to #Community Building to get more eyes on it. 👀
Though the query isn’t counting Likes properly, or at least not all of ours (possibly because we’ve turned Reactions on mid-flow)
| reaction | posts | count |
| --- | --- | --- |
| ❤ | 52295 | 144915 |
| 👍 | 1167 | 1224 |
| 💯 | 1101 | 1236 |
versus:
| reaction\_value | count |
| --- | --- |
| ❤ | 1371442 |
| 💯 | 1236 |
| 👍 | 1224 |
> **FWIW I've been using a small variation of this one to produce our table:**
>
> ```sql
> SELECT source.reaction_value,
> count
> FROM
>
> (
> (
>
> SELECT
> CASE WHEN post_action_type_id = 2 THEN 'heart' END AS reaction_value,
> COUNT(*) AS count
> FROM post_actions
> WHERE post_action_type_id = 2
> AND deleted_at IS NULL
> GROUP BY 1
>
> )
> UNION ALL
> (
>
> SELECT
> reaction_value,
> SUM(reaction_users_count) AS count
> FROM discourse_reactions_reactions
> WHERE reaction_value <> 'heart'
> GROUP BY 1
>
> )
> ) AS source
>
> GROUP BY 1,2
> ORDER BY 2 DESC
>
> ```
There’s also the stock one in the reports section too to cross-check (but the table isn’t as easy to copy and paste 🙂) - `/admin/reports/reactions`
---
### Author:  [@jericson](https://meta.discourse.org/u/jericson)
#### Post date: [11 בדצמבר, 2023, 5:58pm UTC](https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866/3 "2023-12-11T17:58:03Z")
Ah. UNION ALL is the right way to do this query since you are counting two very different things. My query needs an outer join to cover posts that don’t have an entry in `discourse_reactions_reactions`. Adding that caused my query to time out, so I can’t verify that solves the problem. Union is the right answer here in any case.
I’m a bit curious if `post_actions` might overcount likes, though. Does it account for people liking a post and then removing or changing that reaction?
I needed to change ‘heart’ to ‘+1’ in the query since we used 👍 for our likes at College Confidential. Here’s my result from that query:
| reaction | count |
| --- | --- |
| 👍 | 2089798 |
| ❤️🩹 | 28167 |
| 💯 | 27070 |
| ❤ | 24676 |
| 🎉 | 24055 |
| 🤣 | 16778 |
| 😲 | 5325 |
| 💡 | 4590 |
| 😠 | 3547 |
| 😄 | 3078 |
| 😂 | 2623 |
| 😭 | 2443 |
| 😉 | 369 |
| ✔ | 43 |
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [13 בדצמבר, 2023, 11:25am UTC](https://meta.discourse.org/t/which-reactions-are-the-most-commonly-used-in-your-community/286866/4 "2023-12-13T11:25:53Z")
> [@jericson](#):
>
> I’m a bit curious if `post_actions` might overcount likes, though. Does it account for people liking a post and then removing or changing that reaction?
That’s a good point. Let me go back and slip a `AND deleted_at IS NULL` in.