Artist Royalties for May 2024 — SQL Joins Problem
The music app pays artists per stream: ₹0.40 for a stream by a premium listener and ₹0.10 for a stream by a free listener.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #484
Problem statement
The music app pays artists per stream: ₹0.40 for a stream by a premium listener and ₹0.10 for a stream by a free listener. Only plays of at least 30 seconds that started in May 2024 are paid.
Return every artist — including artists with nothing paid this month — with the columns artist_name, paid_streams (the number of paid plays) and royalty_inr (the payout, rounded to 2 decimal places, 0 when nothing is paid). Order by royalty_inr from highest to lowest, then by artist_name.
Tables
Table: Artist
| Column | Type |
|---|---|
| artist_id | int |
| artist_name | varchar |
Primary key: artist_id.
One row per artist on the royalty statement; names are unique.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| artist_id | int |
| listener_tier | enum(free, premium) |
| played_at | datetime |
| seconds_played | int |
Primary key: play_id.
One row per play. listener_tier is the listener's plan at the time of the play.
Examples
Example 1
Artist
| artist_id | artist_name |
|---|---|
| 1 | Ritviz |
| 2 | The Local Train |
| 3 | Arijit Singh |
| 4 | Lost Stories |
Play
| play_id | artist_id | listener_tier | played_at | seconds_played |
|---|---|---|---|---|
| 1 | 1 | premium | 2024-05-01 00:00:10 | 180 |
| 2 | 1 | free | 2024-05-12 18:20:00 | 45 |
| 3 | 1 | premium | 2024-04-30 23:59:00 | 200 |
| 4 | 2 | free | 2024-05-20 09:15:00 | 30 |
| 5 | 2 | free | 2024-05-21 10:00:00 | 29 |
| 6 | 3 | premium | 2024-05-31 23:59:59 | 240 |
| 7 | 3 | free | 2024-06-01 00:00:05 | 240 |
| 8 | 4 | premium | 2024-05-03 12:00:00 | 10 |
Output
| artist_name | paid_streams | royalty_inr |
|---|---|---|
| Ritviz | 2 | 0.5 |
| Arijit Singh | 1 | 0.4 |
| The Local Train | 1 | 0.1 |
| Lost Stories | 0 | 0 |
How to solve Artist Royalties for May 2024
The statement lists every artist, paid or not, so the plays are attached with a LEFT JOIN and every qualifying condition — the 30-second minimum and the May window — goes in the ON clause. If those tests were in WHERE, an artist whose plays were all too short or out of the month would lose its only (NULL-padded) row and vanish from the statement.
Per artist, COUNT(p.play_id) counts only real matches (it ignores the NULL from an unmatched artist), and SUM(CASE …) adds ₹0.40 or ₹0.10 per play by tier; the ELSE 0 makes an artist with no match sum to 0 rather than NULL. The half-open window >= '2024-05-01' AND < '2024-06-01' includes the last second of the 31st and nothing from June 1st.
The alternative aggregates in paise inside a derived table and divides by 100, which keeps the arithmetic exact, then fills missing artists with COALESCE. Both read the plays once.
Reference solution (MySQL)
SELECT a.artist_name, COUNT(p.play_id) AS paid_streams,
ROUND(SUM(CASE WHEN p.listener_tier = 'premium' THEN 0.40
WHEN p.listener_tier = 'free' THEN 0.10
ELSE 0 END), 2) AS royalty_inr
FROM Artist a
LEFT JOIN Play p
ON p.artist_id = a.artist_id
AND p.seconds_played >= 30
AND p.played_at >= '2024-05-01' AND p.played_at < '2024-06-01'
GROUP BY a.artist_id, a.artist_name
ORDER BY royalty_inr DESC, a.artist_nameAnother way
SELECT a.artist_name, COALESCE(s.paid, 0) AS paid_streams, ROUND(COALESCE(s.cents, 0) / 100, 2) AS royalty_inr
FROM Artist a LEFT JOIN (
SELECT artist_id, COUNT(*) AS paid, SUM(IF(listener_tier = 'premium', 40, 10)) AS cents FROM Play
WHERE seconds_played >= 30 AND YEAR(played_at) = 2024 AND MONTH(played_at) = 5 GROUP BY artist_id
) s ON s.artist_id = a.artist_id
ORDER BY royalty_inr DESC, a.artist_name← Most-Streamed Artist in Every Genre · Classify Every Plan Change as Upgrade or Downgrade →