Ad Campaigns Still Live at the Quarter Close

Easy
273.

Ad Campaigns Still Live at the Quarter Close

EasyBasicsDates

MySQL

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

ColumnTypeValues
campaign_idint
campaign_namevarchar
channelenumsearch, social, display, video, email
statusenumactive, paused, ended, draft
start_datedate
end_datedate

Primary key: campaign_id.

One row per ad campaign. end_date is NULL for an always-on campaign with no planned end.

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_namechannelstatusstart_dateend_date
1Monsoon Footwearsocialactive2024-06-012024-07-15
2IPL Jersey Dropvideoactive2024-03-202024-05-26
3Back to Collegesearchactive2024-06-10NULL
4Summer Cooler Pushdisplaypaused2024-04-01NULL
5Independence Day Salesocialactive2024-08-012024-08-16
6Exam Season Notessearchactive2024-05-012024-06-30
7Holi Colours Kitemaildraft2024-06-152024-07-01

Output

campaign_idcampaign_namechannel
1Monsoon Footwearsocial
3Back to Collegesearch
6Exam Season Notessearch
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.

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

ColumnType
campaign_idint
campaign_namevarchar
channelenum(search, social, display, video, email)
statusenum(active, paused, ended, draft)
start_datedate
end_datedate

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_idcampaign_namechannelstatusstart_dateend_date
1Monsoon Footwearsocialactive2024-06-012024-07-15
2IPL Jersey Dropvideoactive2024-03-202024-05-26
3Back to Collegesearchactive2024-06-10NULL
4Summer Cooler Pushdisplaypaused2024-04-01NULL
5Independence Day Salesocialactive2024-08-012024-08-16
6Exam Season Notessearchactive2024-05-012024-06-30
7Holi Colours Kitemaildraft2024-06-152024-07-01

Output

campaign_idcampaign_namechannel
1Monsoon Footwearsocial
3Back to Collegesearch
6Exam Season Notessearch

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_id

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

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