Spotify: Ad Load by Market Against the Four-an-Hour Cap

Medium
935.

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. 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

ColumnType
listener_idint
marketchar

Primary key: listener_id.

One row per free-tier listener and their two-letter market code.

Table: AdSupportedSession

ColumnType
session_idint
listener_idint
session_datedate
minutesint

Primary key: session_id.

One row per listening session on the free tier, with its length in whole minutes.

Table: AdImpression

ColumnType
impression_idint
listener_idint
served_atdatetime

Primary key: impression_id.

One row per audio ad played to a listener.

Example

The rows must come back in the order shown.

Listener

listener_idmarket
1IN
2IN
3BR
4US
5DE

AdSupportedSession

session_idlistener_idsession_dateminutes
1012024-10-0245
1122024-10-0315
1232024-10-0530
1332024-09-3090
1442024-10-3160

AdImpression

impression_idlistener_idserved_at
10012024-10-02 08:10:00
10112024-10-02 08:25:00
10222024-10-03 21:00:00
10322024-10-03 21:07:00
10432024-10-05 12:00:00
10532024-10-05 12:12:00
10632024-10-05 12:20:00
10732024-11-01 00:00:05
10852024-10-09 09:00:00

Output

marketlistening_minutesads_servedload_status
BR303over cap
IN604within cap
US600within cap
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.

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

ColumnType
listener_idint
marketchar

Primary key: listener_id.

One row per free-tier listener and their two-letter market code.

Table: AdSupportedSession

ColumnType
session_idint
listener_idint
session_datedate
minutesint

Primary key: session_id.

One row per listening session on the free tier, with its length in whole minutes.

Table: AdImpression

ColumnType
impression_idint
listener_idint
served_atdatetime

Primary key: impression_id.

One row per audio ad played to a listener.

Examples

Example 1

Listener

listener_idmarket
1IN
2IN
3BR
4US
5DE

AdSupportedSession

session_idlistener_idsession_dateminutes
1012024-10-0245
1122024-10-0315
1232024-10-0530
1332024-09-3090
1442024-10-3160

AdImpression

impression_idlistener_idserved_at
10012024-10-02 08:10:00
10112024-10-02 08:25:00
10222024-10-03 21:00:00
10322024-10-03 21:07:00
10432024-10-05 12:00:00
10532024-10-05 12:12:00
10632024-10-05 12:20:00
10732024-11-01 00:00:05
10852024-10-09 09:00:00

Output

marketlistening_minutesads_servedload_status
BR303over cap
IN604within cap
US600within 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.market

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