Longest Streak of Heavy Data Days — SQL Problem
A subscriber has a heavy day when they use at least 1024 MB (1 GB) of data.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #516
Problem statement
A subscriber has a heavy day when they use at least 1024 MB (1 GB) of data. The analytics team wants each heavy user's longest run of heavy days on consecutive calendar dates; a day without a row, or a day under 1024 MB, breaks the run.
For every subscriber with at least one heavy day, return subscriber_id, longest_streak (the number of days in their longest run) and streak_start (the first date of that run). If two runs are equally long, report the earlier one. Order by subscriber_id.
Tables
Table: DailyData
| Column | Type |
|---|---|
| subscriber_id | int |
| usage_date | date |
| mb_used | int |
Primary key: subscriber_id, usage_date.
One row per subscriber per day with any data use.
Examples
Example 1
DailyData
| subscriber_id | usage_date | mb_used |
|---|---|---|
| 1 | 2024-07-01 | 1500 |
| 1 | 2024-07-02 | 2048 |
| 1 | 2024-07-03 | 900 |
| 1 | 2024-07-04 | 1100 |
| 1 | 2024-07-05 | 1024 |
| 1 | 2024-07-06 | 3000 |
| 2 | 2024-07-01 | 1200 |
| 2 | 2024-07-03 | 1300 |
| 3 | 2024-07-01 | 500 |
| 4 | 2024-07-30 | 1024 |
| 4 | 2024-07-31 | 1024 |
| 4 | 2024-08-01 | 2000 |
Output
| subscriber_id | longest_streak | streak_start |
|---|---|---|
| 1 | 3 | 2024-07-04 |
| 2 | 1 | 2024-07-01 |
| 4 | 3 | 2024-07-30 |
How to solve Longest Streak of Heavy Data Days
This is the gaps-and-islands pattern. Keep only heavy days, number each subscriber's heavy days in date order with ROW_NUMBER(), and subtract that number from the date (as a day count, DATEDIFF(usage_date, '2024-01-01')). Inside a run of consecutive dates both the date and the row number go up by one each day, so the difference is constant; any break — a missing day or a light day that was filtered out — makes the date jump further than the row number, and the difference changes. Grouping by (subscriber_id, grp) therefore yields one row per run, with MIN(usage_date) as its start and COUNT(*) as its length. Runs cross month ends naturally, because the arithmetic is on day counts.
A second ROW_NUMBER(), ordered by length descending and start ascending, picks one run per subscriber and applies the tie rule. Subscribers with no heavy days never enter heavy, so they are absent.
The alternative marks the start of each run with LAG (a row whose previous heavy day is not exactly yesterday starts a run), turns the marks into run numbers with a running SUM, and picks the winner with NOT EXISTS. Both are a couple of sorts over the heavy days.
Reference solution (MySQL)
WITH heavy AS (
SELECT subscriber_id, usage_date,
DATEDIFF(usage_date, '2024-01-01')
- ROW_NUMBER() OVER (PARTITION BY subscriber_id ORDER BY usage_date) AS grp
FROM DailyData
WHERE mb_used >= 1024
), runs AS (
SELECT subscriber_id, MIN(usage_date) AS start_date, COUNT(*) AS len
FROM heavy
GROUP BY subscriber_id, grp
), ranked AS (
SELECT subscriber_id, start_date, len,
ROW_NUMBER() OVER (PARTITION BY subscriber_id ORDER BY len DESC, start_date) AS rn
FROM runs
)
SELECT subscriber_id, len AS longest_streak, start_date AS streak_start
FROM ranked
WHERE rn = 1
ORDER BY subscriber_idAnother way
WITH heavy AS (
SELECT subscriber_id, usage_date,
LAG(usage_date) OVER (PARTITION BY subscriber_id ORDER BY usage_date) AS prev_day
FROM DailyData WHERE mb_used >= 1024
), marked AS (
SELECT subscriber_id, usage_date,
SUM(CASE WHEN prev_day IS NOT NULL AND DATEDIFF(usage_date, prev_day) = 1 THEN 0 ELSE 1 END)
OVER (PARTITION BY subscriber_id ORDER BY usage_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_no
FROM heavy
), runs AS (
SELECT subscriber_id, run_no, MIN(usage_date) AS start_date, COUNT(*) AS len FROM marked GROUP BY subscriber_id, run_no
)
SELECT r.subscriber_id, r.len AS longest_streak, r.start_date AS streak_start
FROM runs r
WHERE NOT EXISTS (
SELECT 1 FROM runs o WHERE o.subscriber_id = r.subscriber_id
AND (o.len > r.len OR (o.len = r.len AND o.start_date < r.start_date))
)
ORDER BY r.subscriber_id← Prepaid Service Lapses Between Recharges · Feeder Downtime After Merging Overlapping Outages →