Creators Whose Latest Post Underperformed — SQL Problem
Creator success managers reach out when a creator's latest post (greatest posted_at; no creator has two posts at the same moment) got fewer likes than the…
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #439
Problem statement
Creator success managers reach out when a creator's latest post (greatest posted_at; no creator has two posts at the same moment) got fewer likes than the average of all their earlier posts. Creators with a single post have no earlier posts and are never flagged.
Return the columns creator_id, latest_post_id, latest_likes and earlier_avg (the earlier posts' average likes, rounded to 1 decimal), in any order.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| creator_id | int |
| posted_at | datetime |
| likes | int |
Primary key: post_id.
One row per post; posted_at is unique within a creator and likes is never NULL.
Examples
Example 1
posts
| post_id | creator_id | posted_at | likes |
|---|---|---|---|
| 1 | 1 | 2025-02-01 18:00:00 | 400 |
| 2 | 1 | 2025-02-08 18:00:00 | 600 |
| 3 | 1 | 2025-02-15 18:00:00 | 450 |
| 4 | 2 | 2025-02-03 09:00:00 | 120 |
| 5 | 2 | 2025-02-10 09:00:00 | 200 |
| 6 | 3 | 2025-02-05 21:00:00 | 900 |
| 7 | 4 | 2025-02-02 12:00:00 | 300 |
| 8 | 4 | 2025-02-09 12:00:00 | 301 |
| 9 | 4 | 2025-02-16 12:00:00 | 300 |
Output
| creator_id | latest_post_id | latest_likes | earlier_avg |
|---|---|---|---|
| 1 | 3 | 450 | 500 |
| 4 | 9 | 300 | 300.5 |
How to solve Creators Whose Latest Post Underperformed
Pick each creator's latest post with a correlated subquery, p.posted_at = (SELECT MAX(posted_at) … same creator). For that post, the earlier average is AVG(likes) over the same creator's posts with a smaller posted_at — another correlated subquery — and the post is flagged when its likes are strictly lower.
Creators with one post need no special case: the earlier set is empty, AVG over no rows is NULL, and likes < NULL is unknown, so the row is filtered out. Likes equal to the average are not "fewer".
The window alternative computes both facts in one pass: ROW_NUMBER by posted_at DESC marks the latest post, and AVG(likes) with the frame ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING averages everything before each row. The third joins every earlier post to the latest one and aggregates, keeping the latest via NOT EXISTS of a later post. The average is rounded to one decimal for display.
Reference solution (MySQL)
SELECT p.creator_id, p.post_id AS latest_post_id, p.likes AS latest_likes,
ROUND((SELECT AVG(q.likes) FROM posts q
WHERE q.creator_id = p.creator_id AND q.posted_at < p.posted_at), 1) AS earlier_avg
FROM posts p
WHERE p.posted_at = (SELECT MAX(q.posted_at) FROM posts q WHERE q.creator_id = p.creator_id)
AND p.likes < (SELECT AVG(q.likes) FROM posts q
WHERE q.creator_id = p.creator_id AND q.posted_at < p.posted_at)Another way
SELECT creator_id, post_id AS latest_post_id, likes AS latest_likes, ROUND(earlier_avg, 1) AS earlier_avg
FROM (
SELECT creator_id, post_id, likes,
ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY posted_at DESC) AS rn,
AVG(likes) OVER (PARTITION BY creator_id ORDER BY posted_at ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS earlier_avg
FROM posts
) t
WHERE rn = 1 AND likes < earlier_avgAnother way
SELECT l.creator_id, l.post_id AS latest_post_id, l.likes AS latest_likes, ROUND(AVG(e.likes), 1) AS earlier_avg
FROM posts l
JOIN posts e ON e.creator_id = l.creator_id AND e.posted_at < l.posted_at
WHERE NOT EXISTS (SELECT 1 FROM posts x WHERE x.creator_id = l.creator_id AND x.posted_at > l.posted_at)
GROUP BY l.creator_id, l.post_id, l.likes
HAVING l.likes < AVG(e.likes)← Most Common Leading Hashtags in Captions · Group Chats Silent for More Than 30 Days →