Dark Days Inside Campaign Flight Dates — SQL Dates Problem
Every campaign is booked to run on each day of its flight, from flight_start to flight_end inclusive.
- Difficulty: Hard
- Topics: Dates, Joins
- Dialect: MySQL
- Problem: #296
Problem statement
Every campaign is booked to run on each day of its flight, from flight_start to flight_end inclusive. A dark day is a flight day on which the campaign spent nothing: it has no spend row, or its spend row shows 0. Spend outside the flight doesn't matter.
Return campaign_id and dark_day for every dark day of every campaign. Order by campaign_id, then dark_day.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| flight_start | date |
| flight_end | date |
Primary key: campaign_id.
One row per campaign; flights are at most three weeks long and flight_end is never before flight_start.
Table: DailySpend
| Column | Type |
|---|---|
| campaign_id | int |
| spend_date | date |
| spend | int |
Primary key: campaign_id, spend_date.
One row per campaign per day the ad server reported, in rupees (0 when the ads were disapproved all day).
Examples
Example 1
Campaign
| campaign_id | campaign_name | flight_start | flight_end |
|---|---|---|---|
| 1 | Ganesh Chaturthi Decor | 2024-09-05 | 2024-09-09 |
| 2 | Onam Festive Deals | 2024-09-10 | 2024-09-11 |
| 3 | Durga Puja Specials | 2024-10-01 | 2024-10-01 |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-09-04 | 5000 |
| 1 | 2024-09-05 | 8000 |
| 1 | 2024-09-07 | 0 |
| 1 | 2024-09-08 | 9000 |
| 2 | 2024-09-10 | 6000 |
| 2 | 2024-09-11 | 6500 |
| 3 | 2024-10-02 | 4000 |
Output
| campaign_id | dark_day |
|---|---|
| 1 | 2024-09-06 |
| 1 | 2024-09-07 |
| 1 | 2024-09-09 |
| 3 | 2024-10-01 |
How to solve Dark Days Inside Campaign Flight Dates
The answer consists of days that may have no row at all, so they must be generated. A recursive CTE seeds one row per campaign at flight_start and repeatedly adds DATE_ADD(day, INTERVAL 1 DAY) while day < flight_end, producing every flight day exactly once — a one-day flight is just its seed. The recursion stops because every campaign's day climbs towards a fixed end, and flights are short.
LEFT JOIN those days to DailySpend on campaign and date. A day without a report comes back with s.spend NULL; a disapproved day has spend = 0; both are dark. Spend rows outside the flight never match a generated day, so they are ignored without a filter.
The alternative generates a small numbers table 0–30 once and joins it to every campaign with flight_start + k ≤ flight_end, then keeps days with no positive spend via NOT EXISTS — the same calendar, built from offsets instead of per-campaign recursion. Both cost one probe per flight day.
Reference solution (MySQL)
WITH RECURSIVE flight AS (
SELECT campaign_id, flight_start AS day, flight_end FROM Campaign
UNION ALL
SELECT campaign_id, DATE_ADD(day, INTERVAL 1 DAY), flight_end FROM flight WHERE day < flight_end
)
SELECT f.campaign_id, f.day AS dark_day
FROM flight f
LEFT JOIN DailySpend s ON s.campaign_id = f.campaign_id AND s.spend_date = f.day
WHERE s.spend IS NULL OR s.spend = 0
ORDER BY f.campaign_id, f.dayAnother way
WITH RECURSIVE n AS (SELECT 0 AS k UNION ALL SELECT k + 1 FROM n WHERE k < 30)
SELECT c.campaign_id, DATE_ADD(c.flight_start, INTERVAL n.k DAY) AS dark_day
FROM Campaign c JOIN n ON DATE_ADD(c.flight_start, INTERVAL n.k DAY) <= c.flight_end
WHERE NOT EXISTS (SELECT 1 FROM DailySpend s WHERE s.campaign_id = c.campaign_id
AND s.spend_date = DATE_ADD(c.flight_start, INTERVAL n.k DAY) AND s.spend > 0)
ORDER BY c.campaign_id, dark_day← Median Daily Spend of Each Channel · Creators Booked by Two Rival Brands at Once →