Median Likes per Creator — SQL Window Functions Problem
Averages are skewed by one viral post, so the creator dashboard shows the median likes instead.
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #446
Problem statement
Averages are skewed by one viral post, so the creator dashboard shows the median likes instead. For a creator with n posts that have a like count, sort those counts: the median is the middle value when n is odd and the average of the two middle values when n is even. Posts with NULL likes (counter not yet synced) are ignored; creators with no counted posts do not appear.
Return the columns creator_id, posts (the n above) and median_likes (rounded to 1 decimal), in any order.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| creator_id | int |
| posted_on | date |
| likes | int |
Primary key: post_id.
One row per post; likes is NULL while the counter syncs.
Examples
Example 1
posts
| post_id | creator_id | posted_on | likes |
|---|---|---|---|
| 1 | 1 | 2025-04-01 | 120 |
| 2 | 1 | 2025-04-03 | 45000 |
| 3 | 1 | 2025-04-05 | 300 |
| 4 | 2 | 2025-04-02 | 80 |
| 5 | 2 | 2025-04-04 | 95 |
| 6 | 2 | 2025-04-06 | 80 |
| 7 | 2 | 2025-04-08 | 210 |
| 8 | 3 | 2025-04-03 | NULL |
| 9 | 4 | 2025-04-09 | 61 |
Output
| creator_id | posts | median_likes |
|---|---|---|
| 1 | 3 | 300 |
| 2 | 4 | 87.5 |
| 4 | 1 | 61 |
How to solve Median Likes per Creator
MySQL has no MEDIAN aggregate, so build it from positions. ROW_NUMBER() over each creator's counted posts, ordered by likes (with post_id to make equal counts take distinct positions), gives every post its rank, and COUNT(*) OVER (PARTITION BY creator_id) puts n on every row. Filtering NULL likes before the window is essential: otherwise NULLs would take positions and shift the middle.
The middle positions are FLOOR((n + 1) / 2) and FLOOR(n / 2) + 1: for odd n both are the same middle row, for even n they are the two central rows. Averaging the selected rows per creator returns the median in both cases — equal values sharing the middle do not matter, because only the values are averaged.
The alternative computes each row's position with a correlated count (rows strictly smaller, plus equal ones with a smaller or equal id) and keeps positions where 2·pos is n, n + 1 or n + 2 — the same middle rows. It is O(n²) per creator against O(n log n) for the window version.
Reference solution (MySQL)
WITH ranked AS (
SELECT creator_id, likes,
ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY likes, post_id) AS rn,
COUNT(*) OVER (PARTITION BY creator_id) AS n
FROM posts
WHERE likes IS NOT NULL
)
SELECT creator_id, MAX(n) AS posts, ROUND(AVG(likes), 1) AS median_likes
FROM ranked
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR(n / 2) + 1)
GROUP BY creator_idAnother way
SELECT creator_id, MAX(n) AS posts, ROUND(AVG(likes), 1) AS median_likes
FROM (
SELECT p.creator_id, p.likes,
(SELECT COUNT(*) FROM posts q WHERE q.creator_id = p.creator_id AND q.likes IS NOT NULL
AND (q.likes < p.likes OR (q.likes = p.likes AND q.post_id <= p.post_id))) AS pos,
(SELECT COUNT(*) FROM posts q WHERE q.creator_id = p.creator_id AND q.likes IS NOT NULL) AS n
FROM posts p WHERE p.likes IS NOT NULL
) t
WHERE pos * 2 IN (n, n + 1, n + 2)
GROUP BY creator_id← Reshare Cascades: Size, Depth and Unique Resharers · App Sessions From the Event Stream →