Spotify: Artist Merch Revenue From Shipped Orders

Easy
929.

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. 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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: MerchOrder

ColumnTypeValues
order_idint
artist_idint
itemvarchar
quantityint
unit_priceint
statusenumshipped, refunded, pending

Primary key: order_id.

One row per order line; unit_price is in rupees.

Example

The rows must come back in the order shown.

Artist

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

MerchOrder

order_idartist_iditemquantityunit_pricestatus
1001Tour T-shirt2900shipped
1011Vinyl12400refunded
1022Hoodie11800shipped
1033Poster4300shipped
1043Cap1600shipped
1054Vinyl12600pending
1062Poster20shipped

Output

artist_nameunitsrevenue
Aurora Lanes21800
Bombay Bassline31800
Coral Static51800
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: 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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: MerchOrder

ColumnType
order_idint
artist_idint
itemvarchar
quantityint
unit_priceint
statusenum(shipped, refunded, pending)

Primary key: order_id.

One row per order line; unit_price is in rupees.

Examples

Example 1

Artist

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

MerchOrder

order_idartist_iditemquantityunit_pricestatus
1001Tour T-shirt2900shipped
1011Vinyl12400refunded
1022Hoodie11800shipped
1033Poster4300shipped
1043Cap1600shipped
1054Vinyl12600pending
1062Poster20shipped

Output

artist_nameunitsrevenue
Aurora Lanes21800
Bombay Bassline31800
Coral Static51800

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_name

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