여러분 커뮤니티에서 가장 많이 사용되는 이모지 반응은 무엇인가요?

[jericson (Jon Ericson)]

Continuing the discussion from Reactions on Meta:

I was curious about how reactions are used on a site I work with 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 by looking at which are the most commonly-used emojis in posts. We adjusted the list based on member feedback. 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:

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: .

[JammyDodger]

이 문제를 Community Building 채널로 옮겨서 더 많은 분들의 관심을 받으세요.

다만, 쿼리가 좋아요(Likes)를 제대로 집계하지 못하고 있거나, 적어도 우리 것 중 일부는 제대로 집계하지 못하고 있는 것 같습니다(반응(Reactions)을 중간에 활성화했기 때문일 수 있음)

반응	게시물 수	개수
	52295	144915
	1167	1224
	1101	1236

반면에:

reaction_value	개수
	1371442
	1236
	1224

참고로, 이 쿼리의 작은 변형 버전을 사용해 테이블을 생성하고 있습니다:

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

교차 검증을 위해 보고서 섹션에도 기본 제공되는 쿼리가 있습니다(다만, 이 테이블은 복사해서 붙여넣기가 좀 어렵습니다 ) - /admin/reports/reactions

[jericson (Jon Ericson)]

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

[JammyDodger]

@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.