Ad Campaigns Still Live at the Quarter Close — SQL Problem
The finance team closes the books on 2024-06-30 and needs to accrue the cost of every campaign that is still running that day.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #273
Problem statement
The finance team closes the books on 2024-06-30 and needs to accrue the cost of every campaign that is still running that day.
A campaign is still live on 2024-06-30 when its status is 'active', its start_date is on or before 2024-06-30, and its end_date is either NULL (an always-on campaign) or on or after 2024-06-30. Return campaign_id, campaign_name and channel, ordered by campaign_id.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| channel | enum(search, social, display, video, email) |
| status | enum(active, paused, ended, draft) |
| start_date | date |
| end_date | date |
Primary key: campaign_id.
One row per ad campaign. end_date is NULL for an always-on campaign with no planned end.
Examples
Example 1
Campaign
| campaign_id | campaign_name | channel | status | start_date | end_date |
|---|---|---|---|---|---|
| 1 | Monsoon Footwear | social | active | 2024-06-01 | 2024-07-15 |
| 2 | IPL Jersey Drop | video | active | 2024-03-20 | 2024-05-26 |
| 3 | Back to College | search | active | 2024-06-10 | NULL |
| 4 | Summer Cooler Push | display | paused | 2024-04-01 | NULL |
| 5 | Independence Day Sale | social | active | 2024-08-01 | 2024-08-16 |
| 6 | Exam Season Notes | search | active | 2024-05-01 | 2024-06-30 |
| 7 | Holi Colours Kit | draft | 2024-06-15 | 2024-07-01 |
Output
| campaign_id | campaign_name | channel |
|---|---|---|
| 1 | Monsoon Footwear | social |
| 3 | Back to College | search |
| 6 | Exam Season Notes | search |
How to solve Ad Campaigns Still Live at the Quarter Close
This is a plain filter, and the whole difficulty is in the NULL. A campaign covers the day when the day falls inside [start_date, end_date], both ends included, and it must also be active — a paused or draft campaign spends nothing even if its dates cover the day.
For an always-on campaign end_date is NULL, and any comparison with NULL is unknown, which WHERE treats as false. Writing end_date IS NULL OR end_date >= '2024-06-30' keeps those campaigns. The alternatives replace the NULL by a date far in the future with COALESCE or IFNULL, which turns the check into one BETWEEN.
Because dates are stored as fixed-format text, comparing them as strings is the same as comparing them as dates. The query reads the table once; an index on status or start_date would let it skip rows.
Reference solution (MySQL)
SELECT campaign_id, campaign_name, channel
FROM Campaign
WHERE status = 'active'
AND start_date <= '2024-06-30'
AND (end_date IS NULL OR end_date >= '2024-06-30')
ORDER BY campaign_idAnother way
SELECT campaign_id, campaign_name, channel FROM Campaign WHERE status = 'active' AND '2024-06-30' BETWEEN start_date AND COALESCE(end_date, '9999-12-31') ORDER BY campaign_idAnother way
SELECT campaign_id, campaign_name, channel FROM Campaign WHERE status = 'active' AND DATEDIFF('2024-06-30', start_date) >= 0 AND DATEDIFF(IFNULL(end_date, '2099-12-31'), '2024-06-30') >= 0 ORDER BY campaign_id← Service-Due Day in Each Maintenance Cycle · Click-Through Rate of Every Ad Group →