Spotify: Artist Merch Revenue From Shipped Orders
Artists can sell merchandise from their Spotify profile, and the partnerships team reports how much each artist's store actually sold.
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #929
Problem statement
Artists can sell merchandise from their Spotify profile, and the partnerships team reports how much each artist's store actually sold. Only orders that shipped count — refunded and pending orders do not.
For every artist with at least one shipped order, return artist_name, units (total quantity shipped) and revenue (the sum of quantity * unit_price over shipped orders). Order by revenue 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: MerchOrder
| Column | Type |
|---|---|
| order_id | int |
| artist_id | int |
| item | varchar |
| quantity | int |
| unit_price | int |
| status | enum(shipped, refunded, pending) |
Primary key: order_id.
One row per order line; unit_price is in rupees.
Examples
Example 1
Artist
| artist_id | artist_name |
|---|---|
| 1 | Aurora Lanes |
| 2 | Bombay Bassline |
| 3 | Coral Static |
| 4 | Dusk Riders |
MerchOrder
| order_id | artist_id | item | quantity | unit_price | status |
|---|---|---|---|---|---|
| 100 | 1 | Tour T-shirt | 2 | 900 | shipped |
| 101 | 1 | Vinyl | 1 | 2400 | refunded |
| 102 | 2 | Hoodie | 1 | 1800 | shipped |
| 103 | 3 | Poster | 4 | 300 | shipped |
| 104 | 3 | Cap | 1 | 600 | shipped |
| 105 | 4 | Vinyl | 1 | 2600 | pending |
| 106 | 2 | Poster | 2 | 0 | shipped |
Output
| artist_name | units | revenue |
|---|---|---|
| Aurora Lanes | 2 | 1800 |
| Bombay Bassline | 3 | 1800 |
| Coral Static | 5 | 1800 |
How to solve Spotify: Artist Merch Revenue From Shipped Orders
Revenue is a sum over order lines of quantity * unit_price, so the expression goes inside SUM. Summing unit_price alone would undercount a line of four posters, and multiplying the summed quantity by an average price would be wrong when an artist sells several items at different prices.
The status filter runs in WHERE, before grouping, so a refunded vinyl or a pending order adds nothing. Because the join is an inner one and the filter removes non-shipped lines, an artist whose orders are all refunded or pending has no rows left and is not listed, which is what "at least one shipped order" means. A free giveaway line (price 0) still counts its units.
Order by revenue, highest first, with the artist name breaking ties — in the example all three artists with shipped orders sell exactly ₹1,800, so the names alone decide the order. The alternative aggregates the orders in a derived table first and joins the names afterwards, which is the same plan; grouping per artist_id keeps two artists apart even if a name repeated.
Reference solution (MySQL)
SELECT a.artist_name, SUM(o.quantity) AS units, SUM(o.quantity * o.unit_price) AS revenue
FROM Artist a
JOIN MerchOrder o ON o.artist_id = a.artist_id
WHERE o.status = 'shipped'
GROUP BY a.artist_id, a.artist_name
ORDER BY revenue DESC, a.artist_nameAnother way
SELECT a.artist_name, t.units, t.revenue FROM Artist a JOIN (SELECT artist_id, SUM(quantity) AS units, SUM(quantity * unit_price) AS revenue FROM MerchOrder WHERE status IN ('shipped') GROUP BY artist_id) t ON t.artist_id = a.artist_id ORDER BY t.revenue DESC, a.artist_name← Spotify: Student Plans Due for Re-Verification · Spotify: URL Slugs for Podcast Show Names →