Spotify: Pro-Rata Royalty Pool Payouts for June 2024

Hard
945.

Spotify: Pro-Rata Royalty Pool Payouts for June 2024

In a pro-rata royalty model, each market's royalty pool for the month is shared among artists in proportion to their share of that market's royalty-bearing streams. A stream bears royalties when it was played for at least 30 seconds and is dated in June 2024. An artist's payout from a market is pool_inr × (artist's royalty-bearing streams there) / (all royalty-bearing streams there), and their total payout is the sum over markets. A market with streams but no pool row pays nothing; a pool with no royalty-bearing streams pays no one.

Return every artist with a payout, with the columns artist_name and payout_inr — the total, rounded to 2 decimal places only at the end. Order by payout_inr from highest to lowest, then by artist_name.

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: Artist

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: RoyaltyPool

ColumnType
marketchar
pool_inrint

Primary key: market.

The June 2024 royalty pool of each market, in rupees.

Table: StreamLog

ColumnType
stream_idint
artist_idint
marketchar
streamed_ondate
ms_playedint

Primary key: stream_id.

One row per stream, credited to the track's main artist, with the market it was played in.

Example

The rows must come back in the order shown.

Artist

artist_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static
4Dusk Riders

RoyaltyPool

marketpool_inr
IN90000
US60000
GB40000

StreamLog

stream_idartist_idmarketstreamed_onms_played
11IN2024-06-01200000
21IN2024-06-1531000
32IN2024-06-3030000
42IN2024-06-3029000
51US2024-06-04120000
63US2024-06-09150000
73US2024-07-01150000
84BR2024-06-12180000
93US2024-05-31200000

Output

artist_namepayout_inr
Aurora Lanes90000
Bombay Bassline30000
Coral Static30000
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: Pro-Rata Royalty Pool Payouts for June 2024

In a pro-rata royalty model, each market's royalty pool for the month is shared among artists in proportion to their share of that market's royalty-bearing…

  • Difficulty: Hard
  • Topics: Aggregation, Joins, Window Functions
  • Dialect: MySQL
  • Problem: #945

Problem statement

In a pro-rata royalty model, each market's royalty pool for the month is shared among artists in proportion to their share of that market's royalty-bearing streams. A stream bears royalties when it was played for at least 30 seconds and is dated in June 2024. An artist's payout from a market is pool_inr × (artist's royalty-bearing streams there) / (all royalty-bearing streams there), and their total payout is the sum over markets. A market with streams but no pool row pays nothing; a pool with no royalty-bearing streams pays no one.

Return every artist with a payout, with the columns artist_name and payout_inr — the total, rounded to 2 decimal places only at the end. Order by payout_inr from highest to lowest, then by artist_name.

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: Artist

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: RoyaltyPool

ColumnType
marketchar
pool_inrint

Primary key: market.

The June 2024 royalty pool of each market, in rupees.

Table: StreamLog

ColumnType
stream_idint
artist_idint
marketchar
streamed_ondate
ms_playedint

Primary key: stream_id.

One row per stream, credited to the track's main artist, with the market it was played in.

Examples

Example 1

Artist

artist_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static
4Dusk Riders

RoyaltyPool

marketpool_inr
IN90000
US60000
GB40000

StreamLog

stream_idartist_idmarketstreamed_onms_played
11IN2024-06-01200000
21IN2024-06-1531000
32IN2024-06-3030000
42IN2024-06-3029000
51US2024-06-04120000
63US2024-06-09150000
73US2024-07-01150000
84BR2024-06-12180000
93US2024-05-31200000

Output

artist_namepayout_inr
Aurora Lanes90000
Bombay Bassline30000
Coral Static30000

How to solve Spotify: Pro-Rata Royalty Pool Payouts for June 2024

The pro-rata formula needs two counts at different grains: royalty-bearing streams per (artist, market) and per market. Compute the qualifying streams once — at least 30,000 ms and dated in June, so 29,999 ms, 31 May and 1 July are all out — then aggregate them both ways. The window form does it in one pass: grouping by artist and market gives COUNT(*), and SUM(COUNT(*)) OVER (PARTITION BY market) adds those counts across the market.

Each (artist, market) row earns pool_inr * n / total; in MySQL / is exact decimal division, so the share is not truncated. Joining RoyaltyPool with an inner join drops markets with no pool (BR in the example), so Dusk Riders, who streamed only there, earns nothing and is not listed. A pool whose market has no qualifying streams never meets an artist row. Summing per artist and rounding once at the end avoids rounding drift; rounding each market's share first could move the total by a paisa.

In the example India's ₹90,000 is split 2:1 between Aurora Lanes and Bombay Bassline (the 29-second stream is out), and the US pool 1:1 between Aurora Lanes and Coral Static, giving Aurora Lanes ₹90,000. Both queries are a single aggregation over the stream log plus small joins.

Reference solution (MySQL)

WITH paid AS (
  SELECT artist_id, market
  FROM StreamLog
  WHERE ms_played >= 30000 AND streamed_on BETWEEN '2024-06-01' AND '2024-06-30'
), market_total AS (
  SELECT market, COUNT(*) AS total FROM paid GROUP BY market
), artist_market AS (
  SELECT artist_id, market, COUNT(*) AS n FROM paid GROUP BY artist_id, market
)
SELECT a.artist_name, ROUND(SUM(r.pool_inr * am.n / mt.total), 2) AS payout_inr
FROM artist_market am
JOIN market_total mt ON mt.market = am.market
JOIN RoyaltyPool r ON r.market = am.market
JOIN Artist a ON a.artist_id = am.artist_id
GROUP BY a.artist_id, a.artist_name
ORDER BY payout_inr DESC, a.artist_name

Another way

SELECT a.artist_name, ROUND(SUM(r.pool_inr * x.n / x.total), 2) AS payout_inr
FROM (SELECT artist_id, market, COUNT(*) AS n, SUM(COUNT(*)) OVER (PARTITION BY market) AS total
      FROM StreamLog
      WHERE ms_played >= 30000 AND streamed_on >= '2024-06-01' AND streamed_on < '2024-07-01'
      GROUP BY artist_id, market) x
JOIN RoyaltyPool r ON r.market = x.market
JOIN Artist a ON a.artist_id = x.artist_id
GROUP BY a.artist_id, a.artist_name
ORDER BY payout_inr DESC, a.artist_name

← Spotify: Weekly Top 3 Chart With New, Re-Entry, Up and Down Labels · Spotify: Listeners Whose Top Genre Changed From Q1 to Q2 →