Creators Whose Latest Post Underperformed

Medium
439.

Creators Whose Latest Post Underperformed

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

ColumnType
post_idint
creator_idint
posted_atdatetime
likesint

Primary key: post_id.

One row per post; posted_at is unique within a creator and likes is never NULL.

Example

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

posts

post_idcreator_idposted_atlikes
112025-02-01 18:00:00400
212025-02-08 18:00:00600
312025-02-15 18:00:00450
422025-02-03 09:00:00120
522025-02-10 09:00:00200
632025-02-05 21:00:00900
742025-02-02 12:00:00300
842025-02-09 12:00:00301
942025-02-16 12:00:00300

Output

creator_idlatest_post_idlatest_likesearlier_avg
13450500
49300300.5
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.

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

ColumnType
post_idint
creator_idint
posted_atdatetime
likesint

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_idcreator_idposted_atlikes
112025-02-01 18:00:00400
212025-02-08 18:00:00600
312025-02-15 18:00:00450
422025-02-03 09:00:00120
522025-02-10 09:00:00200
632025-02-05 21:00:00900
742025-02-02 12:00:00300
842025-02-09 12:00:00301
942025-02-16 12:00:00300

Output

creator_idlatest_post_idlatest_likesearlier_avg
13450500
49300300.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_avg

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