Dark Days Inside Campaign Flight Dates

Hard
296.

Dark Days Inside Campaign Flight Dates

HardDatesJoins

MySQL

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

ColumnType
campaign_idint
campaign_namevarchar
flight_startdate
flight_enddate

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

ColumnType
campaign_idint
spend_datedate
spendint

Primary key (together): campaign_id, spend_date.

One row per campaign per day the ad server reported, in rupees (0 when the ads were disapproved all day).

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_nameflight_startflight_end
1Ganesh Chaturthi Decor2024-09-052024-09-09
2Onam Festive Deals2024-09-102024-09-11
3Durga Puja Specials2024-10-012024-10-01

DailySpend

campaign_idspend_datespend
12024-09-045000
12024-09-058000
12024-09-070
12024-09-089000
22024-09-106000
22024-09-116500
32024-10-024000

Output

campaign_iddark_day
12024-09-06
12024-09-07
12024-09-09
32024-10-01
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.

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

ColumnType
campaign_idint
campaign_namevarchar
flight_startdate
flight_enddate

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

ColumnType
campaign_idint
spend_datedate
spendint

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_idcampaign_nameflight_startflight_end
1Ganesh Chaturthi Decor2024-09-052024-09-09
2Onam Festive Deals2024-09-102024-09-11
3Durga Puja Specials2024-10-012024-10-01

DailySpend

campaign_idspend_datespend
12024-09-045000
12024-09-058000
12024-09-070
12024-09-089000
22024-09-106000
22024-09-116500
32024-10-024000

Output

campaign_iddark_day
12024-09-06
12024-09-07
12024-09-09
32024-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.day

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