Category Share of Revenue

Difficulty: Easy · Topics: window-functions, aggregation, percent-of-total · Asked at: Amazon, Walmart, Instacart

Problem

For each (category, product), return total revenue, its share of the category’s revenue (pct_of_category), and its share of all revenue (pct_of_total), both as percentages rounded to 1 decimal. Order by category, then revenue descending.

Schema and sample data

CREATE TABLE sales (product TEXT, category TEXT, revenue INTEGER);
INSERT INTO sales VALUES
('laptop','electronics',1200),('laptop','electronics',800),('phone','electronics',1000),
('desk','furniture',400),('chair','furniture',150),('chair','furniture',50),('lamp','furniture',400);

Expected output

Hints

Solution

SELECT category, product,
       SUM(revenue) AS revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (PARTITION BY category), 1) AS pct_of_category,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1)                      AS pct_of_total
FROM sales
GROUP BY category, product
ORDER BY category, revenue DESC, product;

Explanation

Window functions run after GROUP BY, so they can aggregate the aggregates. OVER () with an empty window spans all grouped rows. 100.0 * avoids integer division.

Follow-up questions