Apple: Each Product Line's Share of Store Revenue
Retail finance compares the product mix of each Apple Store — how much of a store's revenue comes from iPhone, Mac, iPad, wearables and services.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #612
Problem statement
Retail finance compares the product mix of each Apple Store — how much of a store's revenue comes from iPhone, Mac, iPad, wearables and services.
For every store and every product line it sold, return store_name, product_line, revenue (the sum of amount_usd) and share_pct, the line's percentage of that store's total revenue, rounded to 1 decimal place. Lines a store did not sell are not listed. Order the rows by store_name, then revenue descending, then product_line.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: RetailSale
| Column | Type |
|---|---|
| sale_id | int |
| store_name | varchar |
| product_line | enum(iPhone, Mac, iPad, Wearables, Services) |
| amount_usd | int |
Primary key: sale_id.
One row per sale at a store's tills; every amount is positive.
Examples
Example 1
RetailSale
| sale_id | store_name | product_line | amount_usd |
|---|---|---|---|
| 1 | Apple BKC | iPhone | 1200 |
| 2 | Apple BKC | iPhone | 800 |
| 3 | Apple BKC | Mac | 1500 |
| 4 | Apple BKC | Services | 500 |
| 5 | Apple Saket | iPad | 600 |
| 6 | Apple Saket | Wearables | 600 |
| 7 | Apple Saket | Mac | 1300 |
Output
| store_name | product_line | revenue | share_pct |
|---|---|---|---|
| Apple BKC | iPhone | 2000 | 50 |
| Apple BKC | Mac | 1500 | 37.5 |
| Apple BKC | Services | 500 | 12.5 |
| Apple Saket | Mac | 1300 | 52 |
| Apple Saket | Wearables | 600 | 24 |
| Apple Saket | iPad | 600 | 24 |
How to solve Apple: Each Product Line's Share of Store Revenue
The share needs two totals at once: the line's revenue (a group per store and line) and the store's revenue (a group per store). Group first by store_name, product_line; then a window function over those grouped rows, SUM(revenue) OVER (PARTITION BY store_name), puts the store total next to every line without collapsing the rows. Windows run after GROUP BY, which is why this works in one SELECT over a derived table.
The share is 100 * revenue / store_total, rounded to one decimal; the shares of a store add up to about 100 (rounding can make them 99.9 or 100.1). Lines a store never sold have no rows and are not listed. Two lines with equal revenue are ordered by name, as the statement fixes.
Without windows, aggregate the store totals in a second derived table and join the two on store_name. Both scan the sales once per aggregate; the window version avoids the second scan.
Reference solution (MySQL)
SELECT store_name, product_line, revenue,
ROUND(100 * revenue / SUM(revenue) OVER (PARTITION BY store_name), 1) AS share_pct
FROM (
SELECT store_name, product_line, SUM(amount_usd) AS revenue
FROM RetailSale
GROUP BY store_name, product_line
) t
ORDER BY store_name, revenue DESC, product_lineAnother way
SELECT l.store_name, l.product_line, l.revenue, ROUND(100 * l.revenue / s.total, 1) AS share_pct
FROM (SELECT store_name, product_line, SUM(amount_usd) AS revenue FROM RetailSale GROUP BY store_name, product_line) l
JOIN (SELECT store_name, SUM(amount_usd) AS total FROM RetailSale GROUP BY store_name) s ON s.store_name = l.store_name
ORDER BY l.store_name, l.revenue DESC, l.product_line← Apple: Component Batches Below Their Pooled Test Yield · Apple: AppleCare Serial Numbers That Fail the Format Check →