Sharp Follower Drops Between Daily Snapshots — SQL Problem
A nightly job snapshots every creator's follower count, though a failed run leaves a day without a snapshot.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #436
Problem statement
A nightly job snapshots every creator's follower count, though a failed run leaves a day without a snapshot. A creator lost more than 5% when a snapshot's followers is below 95% of that creator's previous snapshot (the latest earlier snapshot_date, however many days back).
Return the columns creator_id, snapshot_date, previous_followers, followers and drop_pct — (previous_followers − followers) × 100 ÷ previous_followers rounded to 2 decimals — ordered by creator_id, then snapshot_date. A creator's first snapshot has nothing to compare with and is never returned.
Tables
Table: follower_snapshots
| Column | Type |
|---|---|
| creator_id | int |
| snapshot_date | date |
| followers | int |
Primary key: creator_id, snapshot_date.
One row per creator per day the job ran; followers is always positive.
Examples
Example 1
follower_snapshots
| creator_id | snapshot_date | followers |
|---|---|---|
| 1 | 2025-06-01 | 52000 |
| 1 | 2025-06-02 | 51900 |
| 1 | 2025-06-03 | 48000 |
| 1 | 2025-06-05 | 47800 |
| 2 | 2025-06-01 | 8000 |
| 2 | 2025-06-02 | 7600 |
| 2 | 2025-06-04 | 7100 |
| 3 | 2025-06-03 | 1200 |
Output
| creator_id | snapshot_date | previous_followers | followers | drop_pct |
|---|---|---|---|---|
| 1 | 2025-06-03 | 51900 | 48000 | 7.51 |
| 2 | 2025-06-04 | 7600 | 7100 | 6.58 |
How to solve Sharp Follower Drops Between Daily Snapshots
LAG(followers) OVER (PARTITION BY creator_id ORDER BY snapshot_date) puts the previous snapshot's count beside each row. Because it works on rows, not calendar days, a missing day is skipped over naturally — the previous snapshot is whatever came last before, which is the rule. Partitioning by creator makes each creator's first snapshot get NULL.
The filter has to happen in an outer query. Comparing followers * 100 < previous_followers * 95 keeps it in integers, so a drop of exactly 5% is not "more than 5%"; a NULL previous count makes the comparison unknown and drops the first snapshot.
The percentage divides by the previous count and is rounded to 2 decimals. Without window functions, a correlated subquery fetches the latest earlier snapshot with ORDER BY snapshot_date DESC LIMIT 1 — correct, but one lookup per row instead of one sorted pass.
Reference solution (MySQL)
SELECT creator_id, snapshot_date, previous_followers, followers,
ROUND((previous_followers - followers) * 100 / previous_followers, 2) AS drop_pct
FROM (
SELECT creator_id, snapshot_date, followers,
LAG(followers) OVER (PARTITION BY creator_id ORDER BY snapshot_date) AS previous_followers
FROM follower_snapshots
) s
WHERE followers * 100 < previous_followers * 95
ORDER BY creator_id, snapshot_dateAnother way
SELECT creator_id, snapshot_date, previous_followers, followers,
ROUND((previous_followers - followers) * 100 / previous_followers, 2) AS drop_pct
FROM (
SELECT a.creator_id, a.snapshot_date, a.followers,
(SELECT b.followers FROM follower_snapshots b
WHERE b.creator_id = a.creator_id AND b.snapshot_date < a.snapshot_date
ORDER BY b.snapshot_date DESC LIMIT 1) AS previous_followers
FROM follower_snapshots a
) s
WHERE followers < previous_followers * 0.95
ORDER BY creator_id, snapshot_date← Drive-By Commenters Who Never Liked the Post · Sign-Ups by Email Provider →