Artist Royalties for May 2024

Medium
484.

Artist Royalties for May 2024

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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist on the royalty statement; names are unique.

Table: Play

ColumnTypeValues
play_idint
artist_idint
listener_tierenumfree, premium
played_atdatetime
seconds_playedint

Primary key: play_id.

One row per play. listener_tier is the listener's plan at the time of the play.

Example

The rows must come back in the order shown.

Artist

artist_idartist_name
1Ritviz
2The Local Train
3Arijit Singh
4Lost Stories

Play

play_idartist_idlistener_tierplayed_atseconds_played
11premium2024-05-01 00:00:10180
21free2024-05-12 18:20:0045
31premium2024-04-30 23:59:00200
42free2024-05-20 09:15:0030
52free2024-05-21 10:00:0029
63premium2024-05-31 23:59:59240
73free2024-06-01 00:00:05240
84premium2024-05-03 12:00:0010

Output

artist_namepaid_streamsroyalty_inr
Ritviz20.5
Arijit Singh10.4
The Local Train10.1
Lost Stories00
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.

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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist on the royalty statement; names are unique.

Table: Play

ColumnType
play_idint
artist_idint
listener_tierenum(free, premium)
played_atdatetime
seconds_playedint

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_idartist_name
1Ritviz
2The Local Train
3Arijit Singh
4Lost Stories

Play

play_idartist_idlistener_tierplayed_atseconds_played
11premium2024-05-01 00:00:10180
21free2024-05-12 18:20:0045
31premium2024-04-30 23:59:00200
42free2024-05-20 09:15:0030
52free2024-05-21 10:00:0029
63premium2024-05-31 23:59:59240
73free2024-06-01 00:00:05240
84premium2024-05-03 12:00:0010

Output

artist_namepaid_streamsroyalty_inr
Ritviz20.5
Arijit Singh10.4
The Local Train10.1
Lost Stories00

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_name

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