Apple: Each Product Line's Share of Store Revenue

Medium
612.

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.

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

ColumnTypeValues
sale_idint
store_namevarchar
product_lineenumiPhone, Mac, iPad, Wearables, Services
amount_usdint

Primary key: sale_id.

One row per sale at a store's tills; every amount is positive.

Example

The rows must come back in the order shown.

RetailSale

sale_idstore_nameproduct_lineamount_usd
1Apple BKCiPhone1200
2Apple BKCiPhone800
3Apple BKCMac1500
4Apple BKCServices500
5Apple SaketiPad600
6Apple SaketWearables600
7Apple SaketMac1300

Output

store_nameproduct_linerevenueshare_pct
Apple BKCiPhone200050
Apple BKCMac150037.5
Apple BKCServices50012.5
Apple SaketMac130052
Apple SaketWearables60024
Apple SaketiPad60024
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.

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

ColumnType
sale_idint
store_namevarchar
product_lineenum(iPhone, Mac, iPad, Wearables, Services)
amount_usdint

Primary key: sale_id.

One row per sale at a store's tills; every amount is positive.

Examples

Example 1

RetailSale

sale_idstore_nameproduct_lineamount_usd
1Apple BKCiPhone1200
2Apple BKCiPhone800
3Apple BKCMac1500
4Apple BKCServices500
5Apple SaketiPad600
6Apple SaketWearables600
7Apple SaketMac1300

Output

store_nameproduct_linerevenueshare_pct
Apple BKCiPhone200050
Apple BKCMac150037.5
Apple BKCServices50012.5
Apple SaketMac130052
Apple SaketWearables60024
Apple SaketiPad60024

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_line

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