Paid Media Spend per Channel in March 2024

Easy
279.

Paid Media Spend per Channel in March 2024

Spend is recorded per campaign per day; the channel lives on the campaign. The CMO's monthly deck needs the total spend of each channel for March 2024.

Return channel and total_spend (the sum of spend on days from 2024-03-01 to 2024-03-31) for every channel that spent in March. Channels with no March spend rows are left out. Order by total_spend descending, then channel ascending.

Tables

Table: Campaign

ColumnTypeValues
campaign_idint
campaign_namevarchar
channelenumsearch, social, display, video, email

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key (together): campaign_id, spend_date.

One row per campaign per day it spent; spend is in rupees and campaign_id is always in Campaign.

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_namechannel
1Holi Colours Kitsocial
2Exam Season Notessearch
3Summer Cooler Pushdisplay
4Cricket World Cup Merchsocial

DailySpend

campaign_idspend_datespend
12024-03-2012000
12024-03-2115000
22024-03-0118000
22024-02-299000
32024-04-017000
42024-03-319000

Output

channeltotal_spend
social36000
search18000
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.

Paid Media Spend per Channel in March 2024 — SQL Problem

Spend is recorded per campaign per day; the channel lives on the campaign. The CMO's monthly deck needs the total spend of each channel for March 2024.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #279

Problem statement

Spend is recorded per campaign per day; the channel lives on the campaign. The CMO's monthly deck needs the total spend of each channel for March 2024.

Return channel and total_spend (the sum of spend on days from 2024-03-01 to 2024-03-31) for every channel that spent in March. Channels with no March spend rows are left out. Order by total_spend descending, then channel ascending.

Tables

Table: Campaign

ColumnType
campaign_idint
campaign_namevarchar
channelenum(search, social, display, video, email)

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key: campaign_id, spend_date.

One row per campaign per day it spent; spend is in rupees and campaign_id is always in Campaign.

Examples

Example 1

Campaign

campaign_idcampaign_namechannel
1Holi Colours Kitsocial
2Exam Season Notessearch
3Summer Cooler Pushdisplay
4Cricket World Cup Merchsocial

DailySpend

campaign_idspend_datespend
12024-03-2012000
12024-03-2115000
22024-03-0118000
22024-02-299000
32024-04-017000
42024-03-319000

Output

channeltotal_spend
social36000
search18000

How to solve Paid Media Spend per Channel in March 2024

Join each spend row to its campaign to learn the channel, keep only March's rows, and sum per channel. Filtering in WHERE before the GROUP BY means a channel whose only rows fall outside March produces no group at all, which is exactly "left out".

The month can be written as a half-open range (>= '2024-03-01' AND < '2024-04-01'), as YEAR(...) = 2024 AND MONTH(...) = 3, or as DATE_FORMAT(spend_date, '%Y-%m') = '2024-03'. The range is the one that can use an index on spend_date; the others are easier to read. Several campaigns can share a channel, so grouping by the campaign would split the channel's total.

Equal totals are ordered by the channel name, so the order is complete. Cost: one pass over the spend rows plus a primary-key lookup per row.

Reference solution (MySQL)

SELECT c.channel, SUM(s.spend) AS total_spend
FROM DailySpend s
JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE s.spend_date >= '2024-03-01' AND s.spend_date < '2024-04-01'
GROUP BY c.channel
ORDER BY total_spend DESC, c.channel

Another way

SELECT c.channel, SUM(s.spend) AS total_spend FROM Campaign c JOIN DailySpend s ON s.campaign_id = c.campaign_id AND YEAR(s.spend_date) = 2024 AND MONTH(s.spend_date) = 3 GROUP BY c.channel ORDER BY total_spend DESC, c.channel

Another way

SELECT channel, SUM(spend) AS total_spend FROM (SELECT (SELECT channel FROM Campaign c WHERE c.campaign_id = s.campaign_id) AS channel, spend FROM DailySpend s WHERE DATE_FORMAT(spend_date, '%Y-%m') = '2024-03') t GROUP BY channel ORDER BY total_spend DESC, channel

← Instagram Creator Deals Signed in Q3 2024 · Label Each Campaign by Its Monthly Budget Tier →