Streaks of Three or More Overspent Days — SQL Problem
A day is overspent for a campaign when its spend is strictly greater than the campaign's daily_budget.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #292
Problem statement
A day is overspent for a campaign when its spend is strictly greater than the campaign's daily_budget. Pacing alerts fire only on a streak: three or more consecutive calendar days, all overspent. A day with no spend row breaks a streak, as does a day at or under budget.
Return one row per streak of at least 3 days with campaign_id, streak_start, streak_end and streak_days. Order by campaign_id, then streak_start.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| daily_budget | int |
Primary key: campaign_id.
One row per campaign; daily_budget in rupees.
Table: DailySpend
| Column | Type |
|---|---|
| campaign_id | int |
| spend_date | date |
| spend | int |
Primary key: campaign_id, spend_date.
One row per campaign per day it spent, in rupees.
Examples
Example 1
Campaign
| campaign_id | campaign_name | daily_budget |
|---|---|---|
| 1 | Independence Day Sale | 10000 |
| 2 | Raksha Bandhan Gifts | 15000 |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-08-10 | 12000 |
| 1 | 2024-08-11 | 11000 |
| 1 | 2024-08-12 | 10500 |
| 1 | 2024-08-13 | 10000 |
| 1 | 2024-08-14 | 13000 |
| 1 | 2024-08-15 | 18000 |
| 1 | 2024-08-17 | 14000 |
| 2 | 2024-08-10 | 16000 |
| 2 | 2024-08-11 | 17000 |
| 2 | 2024-08-12 | 15500 |
| 2 | 2024-08-13 | 19000 |
Output
| campaign_id | streak_start | streak_end | streak_days |
|---|---|---|---|
| 1 | 2024-08-10 | 2024-08-12 | 3 |
| 2 | 2024-08-10 | 2024-08-13 | 4 |
How to solve Streaks of Three or More Overspent Days
This is gaps and islands. Filter to the overspent days (spend > daily_budget, so a day exactly at budget is out). Among what remains, a streak is a maximal run of consecutive calendar dates per campaign.
Number those days with ROW_NUMBER() per campaign in date order. Inside a run, the date and the row number both advance by one each day, so spend_date − rn days is the same for the whole run; a gap — a missing date or a filtered-out day — advances the date by more than the row number and starts a new constant. Grouping by (campaign, that constant) yields one row per run, with MIN/MAX/COUNT giving its bounds and length; HAVING COUNT(*) >= 3 keeps the alert-worthy ones.
The first alternative flags a new island whenever the previous overspent date is not exactly one day earlier (LAG + DATEDIFF) and numbers islands with a running sum of those flags — an explicit ROWS frame keeps the sum strictly row by row. The second finds streak starts (no overspent day before) and ends (none after) with anti joins and pairs each start with the first end at or after it. The window versions sort once per campaign; the anti-join version is quadratic in the worst case.
Reference solution (MySQL)
WITH over_days AS (
SELECT s.campaign_id, s.spend_date,
ROW_NUMBER() OVER (PARTITION BY s.campaign_id ORDER BY s.spend_date) AS rn
FROM DailySpend s
JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE s.spend > c.daily_budget
), islands AS (
SELECT campaign_id, spend_date, DATE_SUB(spend_date, INTERVAL rn DAY) AS grp
FROM over_days
)
SELECT campaign_id, MIN(spend_date) AS streak_start, MAX(spend_date) AS streak_end, COUNT(*) AS streak_days
FROM islands
GROUP BY campaign_id, grp
HAVING COUNT(*) >= 3
ORDER BY campaign_id, streak_startAnother way
WITH o AS (
SELECT s.campaign_id, s.spend_date,
LAG(s.spend_date) OVER (PARTITION BY s.campaign_id ORDER BY s.spend_date) AS prev_date
FROM DailySpend s JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE s.spend > c.daily_budget
), f AS (
SELECT campaign_id, spend_date,
SUM(CASE WHEN prev_date IS NOT NULL AND DATEDIFF(spend_date, prev_date) = 1 THEN 0 ELSE 1 END)
OVER (PARTITION BY campaign_id ORDER BY spend_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM o
)
SELECT campaign_id, MIN(spend_date) AS streak_start, MAX(spend_date) AS streak_end, COUNT(*) AS streak_days
FROM f GROUP BY campaign_id, island HAVING COUNT(*) >= 3
ORDER BY campaign_id, streak_startAnother way
WITH o AS (
SELECT s.campaign_id, s.spend_date FROM DailySpend s JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE s.spend > c.daily_budget
), starts AS (
SELECT a.campaign_id, a.spend_date AS streak_start FROM o a
WHERE NOT EXISTS (SELECT 1 FROM o b WHERE b.campaign_id = a.campaign_id AND b.spend_date = DATE_SUB(a.spend_date, INTERVAL 1 DAY))
), ends AS (
SELECT a.campaign_id, a.spend_date AS streak_end FROM o a
WHERE NOT EXISTS (SELECT 1 FROM o b WHERE b.campaign_id = a.campaign_id AND b.spend_date = DATE_ADD(a.spend_date, INTERVAL 1 DAY))
)
SELECT s.campaign_id, s.streak_start, MIN(e.streak_end) AS streak_end, DATEDIFF(MIN(e.streak_end), s.streak_start) + 1 AS streak_days
FROM starts s JOIN ends e ON e.campaign_id = s.campaign_id AND e.streak_end >= s.streak_start
GROUP BY s.campaign_id, s.streak_start
HAVING DATEDIFF(MIN(e.streak_end), s.streak_start) + 1 >= 3
ORDER BY s.campaign_id, s.streak_start← First-Touch vs Last-Touch Revenue by Channel · Ad-Click Sessions per Shopper →