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
| Column | Type |
|---|---|
| artist_id | int |
| artist_name | varchar |
Primary key: artist_id.
One row per artist; names are unique.
Table: RoyaltyPool
| Column | Type |
|---|---|
| market | char |
| pool_inr | int |
Primary key: market.
The June 2024 royalty pool of each market, in rupees.
Table: StreamLog
| Column | Type |
|---|---|
| stream_id | int |
| artist_id | int |
| market | char |
| streamed_on | date |
| ms_played | int |
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_id | artist_name |
|---|---|
| 1 | Aurora Lanes |
| 2 | Bombay Bassline |
| 3 | Coral Static |
| 4 | Dusk Riders |
RoyaltyPool
| market | pool_inr |
|---|---|
| IN | 90000 |
| US | 60000 |
| GB | 40000 |
StreamLog
| stream_id | artist_id | market | streamed_on | ms_played |
|---|---|---|---|---|
| 1 | 1 | IN | 2024-06-01 | 200000 |
| 2 | 1 | IN | 2024-06-15 | 31000 |
| 3 | 2 | IN | 2024-06-30 | 30000 |
| 4 | 2 | IN | 2024-06-30 | 29000 |
| 5 | 1 | US | 2024-06-04 | 120000 |
| 6 | 3 | US | 2024-06-09 | 150000 |
| 7 | 3 | US | 2024-07-01 | 150000 |
| 8 | 4 | BR | 2024-06-12 | 180000 |
| 9 | 3 | US | 2024-05-31 | 200000 |
Output
| artist_name | payout_inr |
|---|---|
| Aurora Lanes | 90000 |
| Bombay Bassline | 30000 |
| Coral Static | 30000 |
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_nameAnother 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 →