Each Creator's Most-Liked Post — SQL Problem
For the year-in-review card, show each creator's single most-liked post.
- Difficulty: Medium
- Topics: Window Functions, Joins
- Dialect: MySQL
- Problem: #433
Problem statement
For the year-in-review card, show each creator's single most-liked post. If two posts have the same likes, the earlier posted_at wins; if they were posted at the same moment, the smaller post_id wins.
Return the columns handle, post_id and likes, one row per creator who has at least one post, in any order.
Tables
Table: creators
| Column | Type |
|---|---|
| creator_id | int |
| handle | varchar |
| category | varchar |
Primary key: creator_id.
One row per creator account.
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| creator_id | int |
| posted_at | datetime |
| likes | int |
Primary key: post_id.
creator_id is always a row of creators; likes is never NULL.
Examples
Example 1
creators
| creator_id | handle | category |
|---|---|---|
| 1 | aarav.eats | food |
| 2 | diya_travels | travel |
| 3 | kabir.codes | tech |
| 4 | zara_ootd | fashion |
posts
| post_id | creator_id | posted_at | likes |
|---|---|---|---|
| 10 | 1 | 2024-03-02 12:00:00 | 5400 |
| 11 | 1 | 2024-06-18 19:30:00 | 8200 |
| 12 | 1 | 2024-09-01 08:00:00 | 8200 |
| 13 | 2 | 2024-04-11 10:00:00 | 3100 |
| 14 | 2 | 2024-04-11 10:00:00 | 3100 |
| 15 | 3 | 2024-12-25 21:00:00 | 950 |
Output
| handle | post_id | likes |
|---|---|---|
| aarav.eats | 11 | 8200 |
| diya_travels | 13 | 3100 |
| kabir.codes | 15 | 950 |
How to solve Each Creator's Most-Liked Post
"The best row per group" is the textbook use of ROW_NUMBER(). Partition the posts by creator and order each partition by the ranking rule — likes DESC, then posted_at, then post_id — so the winner is numbered 1. Because the ordering is total (post_id is unique), exactly one post per creator gets 1, and filtering rn = 1 in an outer query (window functions cannot appear in WHERE) keeps it. A join to creators adds the handle.
RANK() or DENSE_RANK() would be wrong here: on a tie they number several posts 1, and the card has room for one.
Without window functions, the same rule is an anti join: keep a post when no other post of the same creator beats it under the ordering — the three OR branches spell the ordering out. A correlated ORDER BY … LIMIT 1 subquery is a third form. The window query sorts each partition once (O(n log n)); the correlated forms are fine with an index on (creator_id, likes).
Reference solution (MySQL)
WITH ranked AS (
SELECT creator_id, post_id, likes,
ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY likes DESC, posted_at, post_id) AS rn
FROM posts
)
SELECT c.handle, r.post_id, r.likes
FROM ranked r
JOIN creators c ON c.creator_id = r.creator_id
WHERE r.rn = 1Another way
SELECT c.handle, p.post_id, p.likes
FROM posts p JOIN creators c ON c.creator_id = p.creator_id
WHERE NOT EXISTS (
SELECT 1 FROM posts q
WHERE q.creator_id = p.creator_id
AND (q.likes > p.likes
OR (q.likes = p.likes AND q.posted_at < p.posted_at)
OR (q.likes = p.likes AND q.posted_at = p.posted_at AND q.post_id < p.post_id))
)Another way
SELECT c.handle, p.post_id, p.likes
FROM posts p JOIN creators c ON c.creator_id = p.creator_id
WHERE p.post_id = (SELECT q.post_id FROM posts q WHERE q.creator_id = p.creator_id ORDER BY q.likes DESC, q.posted_at, q.post_id LIMIT 1)← Creator Engagement Rate by Month · Moderation Outcomes by Report Reason →