Median Likes per Creator

Hard
446.

Median Likes per Creator

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

ColumnType
post_idint
creator_idint
posted_ondate
likesint

Primary key: post_id.

One row per post; likes is NULL while the counter syncs.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

posts

post_idcreator_idposted_onlikes
112025-04-01120
212025-04-0345000
312025-04-05300
422025-04-0280
522025-04-0495
622025-04-0680
722025-04-08210
832025-04-03NULL
942025-04-0961

Output

creator_idpostsmedian_likes
13300
2487.5
4161
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
post_idint
creator_idint
posted_ondate
likesint

Primary key: post_id.

One row per post; likes is NULL while the counter syncs.

Examples

Example 1

posts

post_idcreator_idposted_onlikes
112025-04-01120
212025-04-0345000
312025-04-05300
422025-04-0280
522025-04-0495
622025-04-0680
722025-04-08210
832025-04-03NULL
942025-04-0961

Output

creator_idpostsmedian_likes
13300
2487.5
4161

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_id

Another 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 →