Each Creator's Most-Liked Post

Medium
433.

Each Creator's Most-Liked Post

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

ColumnType
creator_idint
handlevarchar
categoryvarchar

Primary key: creator_id.

One row per creator account.

Table: posts

ColumnType
post_idint
creator_idint
posted_atdatetime
likesint

Primary key: post_id.

creator_id is always a row of creators; likes is never NULL.

Example

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

creators

creator_idhandlecategory
1aarav.eatsfood
2diya_travelstravel
3kabir.codestech
4zara_ootdfashion

posts

post_idcreator_idposted_atlikes
1012024-03-02 12:00:005400
1112024-06-18 19:30:008200
1212024-09-01 08:00:008200
1322024-04-11 10:00:003100
1422024-04-11 10:00:003100
1532024-12-25 21:00:00950

Output

handlepost_idlikes
aarav.eats118200
diya_travels133100
kabir.codes15950
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.

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

ColumnType
creator_idint
handlevarchar
categoryvarchar

Primary key: creator_id.

One row per creator account.

Table: posts

ColumnType
post_idint
creator_idint
posted_atdatetime
likesint

Primary key: post_id.

creator_id is always a row of creators; likes is never NULL.

Examples

Example 1

creators

creator_idhandlecategory
1aarav.eatsfood
2diya_travelstravel
3kabir.codestech
4zara_ootdfashion

posts

post_idcreator_idposted_atlikes
1012024-03-02 12:00:005400
1112024-06-18 19:30:008200
1212024-09-01 08:00:008200
1322024-04-11 10:00:003100
1422024-04-11 10:00:003100
1532024-12-25 21:00:00950

Output

handlepost_idlikes
aarav.eats118200
diya_travels133100
kabir.codes15950

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 = 1

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