LinkedIn: Most Common Reaction on Each Post — SQL Problem
The feed team studies which reaction each post draws most — celebrate, insightful, support and so on — to tune what the reaction picker shows first.
- Difficulty: Medium
- Topics: Aggregation, Window Functions, Joins
- Dialect: MySQL
- Problem: #734
Problem statement
The feed team studies which reaction each post draws most — celebrate, insightful, support and so on — to tune what the reaction picker shows first.
For every post with at least one reaction, return post_id, author_id, top_reaction (the reaction_type used most often on that post) and top_count (how many times it was used). When two types tie, the one that comes first alphabetically wins. Posts without reactions are not listed. Order the rows by post_id.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: Post
| Column | Type |
|---|---|
| post_id | int |
| author_id | int |
| posted_on | date |
Primary key: post_id.
One row per feed post.
Table: Reaction
| Column | Type |
|---|---|
| post_id | int |
| member_id | int |
| reaction_type | varchar |
| reacted_at | datetime |
Primary key: post_id, member_id.
A member's one reaction on a post. reaction_type is one of celebrate, funny, insightful, like, love, support (always lower case).
Examples
Example 1
Post
| post_id | author_id | posted_on |
|---|---|---|
| 1 | 7 | 2025-03-01 |
| 2 | 7 | 2025-03-03 |
| 3 | 8 | 2025-03-04 |
| 4 | 9 | 2025-03-05 |
Reaction
| post_id | member_id | reaction_type | reacted_at |
|---|---|---|---|
| 1 | 21 | like | 2025-03-01 10:00:00 |
| 1 | 22 | celebrate | 2025-03-01 10:05:00 |
| 1 | 23 | like | 2025-03-01 11:00:00 |
| 1 | 24 | insightful | 2025-03-01 12:00:00 |
| 2 | 21 | support | 2025-03-03 09:00:00 |
| 2 | 25 | insightful | 2025-03-03 09:30:00 |
| 3 | 26 | love | 2025-03-04 18:00:00 |
Output
| post_id | author_id | top_reaction | top_count |
|---|---|---|---|
| 1 | 7 | like | 2 |
| 2 | 7 | insightful | 1 |
| 3 | 8 | love | 1 |
How to solve LinkedIn: Most Common Reaction on Each Post
The question has two levels. Inside a post, reactions are grouped by type and counted. Across the types of one post, the winner is the largest count. That second step is a classic top-1 per group, which ROW_NUMBER() solves: number the types of each post by cnt DESC, reaction_type and keep number 1. Because the type name is part of the ordering, a tie is resolved alphabetically and exactly one row per post survives — RANK() would let both tied types through.
Joining to Post adds the author; posts with no reactions have no counts and drop out of the inner join, as required. The alternative keeps a type when no other type of the same post beats it — more uses, or as many uses and an earlier name — which is the same rule written as a NOT EXISTS. The window version sorts each post's few types once; the correlated version compares every pair of types per post, which is fine for the handful of reaction types.
Reference solution (MySQL)
WITH counts AS (
SELECT post_id, reaction_type, COUNT(*) AS cnt
FROM Reaction
GROUP BY post_id, reaction_type
), ranked AS (
SELECT post_id, reaction_type, cnt,
ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY cnt DESC, reaction_type) AS rn
FROM counts
)
SELECT p.post_id, p.author_id, r.reaction_type AS top_reaction, r.cnt AS top_count
FROM Post p
JOIN ranked r ON r.post_id = p.post_id AND r.rn = 1
ORDER BY p.post_idAnother way
WITH counts AS (SELECT post_id, reaction_type, COUNT(*) AS cnt FROM Reaction GROUP BY post_id, reaction_type)
SELECT p.post_id, p.author_id, c.reaction_type AS top_reaction, c.cnt AS top_count
FROM Post p JOIN counts c ON c.post_id = p.post_id
WHERE NOT EXISTS (
SELECT 1 FROM counts o
WHERE o.post_id = c.post_id
AND (o.cnt > c.cnt OR (o.cnt = c.cnt AND o.reaction_type < c.reaction_type))
)
ORDER BY p.post_id← LinkedIn: Members Whose Profile Views Doubled in May · LinkedIn: Custom Profile URLs That Break the Rules →