Streaks of Three or More Overspent Days

Hard
292.

Streaks of Three or More Overspent Days

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

ColumnType
campaign_idint
campaign_namevarchar
daily_budgetint

Primary key: campaign_id.

One row per campaign; daily_budget in rupees.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key (together): campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_namedaily_budget
1Independence Day Sale10000
2Raksha Bandhan Gifts15000

DailySpend

campaign_idspend_datespend
12024-08-1012000
12024-08-1111000
12024-08-1210500
12024-08-1310000
12024-08-1413000
12024-08-1518000
12024-08-1714000
22024-08-1016000
22024-08-1117000
22024-08-1215500
1 more row

Output

campaign_idstreak_startstreak_endstreak_days
12024-08-102024-08-123
22024-08-102024-08-134
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.

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

ColumnType
campaign_idint
campaign_namevarchar
daily_budgetint

Primary key: campaign_id.

One row per campaign; daily_budget in rupees.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key: campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Examples

Example 1

Campaign

campaign_idcampaign_namedaily_budget
1Independence Day Sale10000
2Raksha Bandhan Gifts15000

DailySpend

campaign_idspend_datespend
12024-08-1012000
12024-08-1111000
12024-08-1210500
12024-08-1310000
12024-08-1413000
12024-08-1518000
12024-08-1714000
22024-08-1016000
22024-08-1117000
22024-08-1215500
22024-08-1319000

Output

campaign_idstreak_startstreak_endstreak_days
12024-08-102024-08-123
22024-08-102024-08-134

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_start

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

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