Sharp Follower Drops Between Daily Snapshots

Medium
436.

Sharp Follower Drops Between Daily Snapshots

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

ColumnType
creator_idint
snapshot_datedate
followersint

Primary key (together): creator_id, snapshot_date.

One row per creator per day the job ran; followers is always positive.

Example

The rows must come back in the order shown.

follower_snapshots

creator_idsnapshot_datefollowers
12025-06-0152000
12025-06-0251900
12025-06-0348000
12025-06-0547800
22025-06-018000
22025-06-027600
22025-06-047100
32025-06-031200

Output

creator_idsnapshot_dateprevious_followersfollowersdrop_pct
12025-06-0351900480007.51
22025-06-04760071006.58
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.

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

ColumnType
creator_idint
snapshot_datedate
followersint

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_idsnapshot_datefollowers
12025-06-0152000
12025-06-0251900
12025-06-0348000
12025-06-0547800
22025-06-018000
22025-06-027600
22025-06-047100
32025-06-031200

Output

creator_idsnapshot_dateprevious_followersfollowersdrop_pct
12025-06-0351900480007.51
22025-06-04760071006.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_date

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