Spotify: Ad Load by Market Against the Four-an-Hour Cap
Ad-supported listening has a target ceiling of four ads per hour of listening.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #935
Problem statement
Ad-supported listening has a target ceiling of four ads per hour of listening. The ads operations team checks each market's load for October 2024: listening minutes come from free-tier sessions dated in October, ads from impressions served in October, and both are credited to the market of the listener.
For every market with at least one October session, return market, listening_minutes, ads_served (0 when no ad was served) and load_status: 'over cap' when the market served more than 4 ads per 60 listening minutes, otherwise 'within cap'. Exactly four an hour is within the cap. Order by market.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: Listener
| Column | Type |
|---|---|
| listener_id | int |
| market | char |
Primary key: listener_id.
One row per free-tier listener and their two-letter market code.
Table: AdSupportedSession
| Column | Type |
|---|---|
| session_id | int |
| listener_id | int |
| session_date | date |
| minutes | int |
Primary key: session_id.
One row per listening session on the free tier, with its length in whole minutes.
Table: AdImpression
| Column | Type |
|---|---|
| impression_id | int |
| listener_id | int |
| served_at | datetime |
Primary key: impression_id.
One row per audio ad played to a listener.
Examples
Example 1
Listener
| listener_id | market |
|---|---|
| 1 | IN |
| 2 | IN |
| 3 | BR |
| 4 | US |
| 5 | DE |
AdSupportedSession
| session_id | listener_id | session_date | minutes |
|---|---|---|---|
| 10 | 1 | 2024-10-02 | 45 |
| 11 | 2 | 2024-10-03 | 15 |
| 12 | 3 | 2024-10-05 | 30 |
| 13 | 3 | 2024-09-30 | 90 |
| 14 | 4 | 2024-10-31 | 60 |
AdImpression
| impression_id | listener_id | served_at |
|---|---|---|
| 100 | 1 | 2024-10-02 08:10:00 |
| 101 | 1 | 2024-10-02 08:25:00 |
| 102 | 2 | 2024-10-03 21:00:00 |
| 103 | 2 | 2024-10-03 21:07:00 |
| 104 | 3 | 2024-10-05 12:00:00 |
| 105 | 3 | 2024-10-05 12:12:00 |
| 106 | 3 | 2024-10-05 12:20:00 |
| 107 | 3 | 2024-11-01 00:00:05 |
| 108 | 5 | 2024-10-09 09:00:00 |
Output
| market | listening_minutes | ads_served | load_status |
|---|---|---|---|
| BR | 30 | 3 | over cap |
| IN | 60 | 4 | within cap |
| US | 60 | 0 | within cap |
How to solve Spotify: Ad Load by Market Against the Four-an-Hour Cap
Two independent facts per market — minutes listened and ads served — live in two different tables, both reached through Listener. Joining all three at once is the classic fan-out bug: a market's every session pairs with every impression, so minutes get multiplied by the number of ads and ads by the number of sessions. The fix is to aggregate each fact on its own, in two CTEs keyed by market, and join the summaries one-to-one.
The date filters go inside each summary. Sessions are dated, so BETWEEN '2024-10-01' AND '2024-10-31' is exact; impressions are timestamps, so a half-open range up to '2024-11-01' keeps 31 October 23:59 and drops 1 November 00:00:05. The answer starts from the minutes summary, because the statement lists markets with October listening; a LEFT JOIN to ads plus COALESCE gives 0 for a market without ads, and a market with ads but no sessions (DE in the example) never appears.
The cap test stays in integers: four per hour means ads * 60 > minutes * 4, or ads * 15 > minutes — no division, no rounding, and exactly four an hour (IN: 4 ads in 60 minutes) is within the cap. The alternative counts each market's ads in a correlated subquery instead of a second summary.
Reference solution (MySQL)
WITH mins AS (
SELECT l.market, SUM(s.minutes) AS listening_minutes
FROM AdSupportedSession s
JOIN Listener l ON l.listener_id = s.listener_id
WHERE s.session_date BETWEEN '2024-10-01' AND '2024-10-31'
GROUP BY l.market
), ads AS (
SELECT l.market, COUNT(*) AS ads_served
FROM AdImpression i
JOIN Listener l ON l.listener_id = i.listener_id
WHERE i.served_at >= '2024-10-01' AND i.served_at < '2024-11-01'
GROUP BY l.market
)
SELECT m.market, m.listening_minutes, COALESCE(a.ads_served, 0) AS ads_served,
CASE WHEN COALESCE(a.ads_served, 0) * 60 > m.listening_minutes * 4 THEN 'over cap' ELSE 'within cap' END AS load_status
FROM mins m
LEFT JOIN ads a ON a.market = m.market
ORDER BY m.marketAnother way
SELECT x.market, x.listening_minutes, x.ads_served, IF(x.ads_served * 15 > x.listening_minutes, 'over cap', 'within cap') AS load_status
FROM (SELECT l.market, SUM(s.minutes) AS listening_minutes,
(SELECT COUNT(*) FROM AdImpression i JOIN Listener l2 ON l2.listener_id = i.listener_id
WHERE l2.market = l.market AND DATE(i.served_at) BETWEEN '2024-10-01' AND '2024-10-31') AS ads_served
FROM AdSupportedSession s JOIN Listener l ON l.listener_id = s.listener_id
WHERE YEAR(s.session_date) = 2024 AND MONTH(s.session_date) = 10
GROUP BY l.market) x
ORDER BY x.market← Spotify: Collaborative Playlists Only the Owner Edits · Spotify: Artists Not Touring Their Top Listening City →