Which products make up 80% of revenue? (share of total)
Show each product's revenue, its percentage of total revenue, and the running (cumulative) percentage from the best seller down. Then list the products that bring in the first 80% of revenue.
What it tests
Window aggregates over the whole result (share of total), a running total with an explicit ROWS frame, and turning a cumulative share into a filter — the classic Pareto analysis.
The pattern
Share of total and cumulative share. "Percentage of total", "share", "contribution", "cumulative", "top products making up X%".
How to approach it
- Total revenue per product in a CTE:
SUM(quantity × price). - Share of total: revenue ÷
SUM(revenue) OVER (), times 100.0. - Cumulative share: a running SUM ordered by revenue descending, with ROWS UNBOUNDED PRECEDING, divided by the total.
- For "the first 80%": keep products whose revenue before them is still under 80% of the total.
Edge cases to mention
- Ties in revenue: the running total needs a tiebreaker (e.g. product name), or tied products share a RANGE frame.
- Products that never sold (the phone case) don't appear; LEFT JOIN from products if they should show 0%.
- Rounding: the rounded percentages may not add up to exactly 100.
Common mistakes
- Integer division:
revenue / SUM(revenue) OVER ()gives 0 for every row unless you multiply by 100.0 first. - Leaving the frame as the default RANGE, so tied revenues get the same cumulative value.
- Filtering
cumulative_pct <= 80, which drops the product that crosses 80% — usually it should be included.
Not true: Filtering on cumulative_pct <= 80 keeps the product that takes the running total past 80%.
In fact: That product's own running share is already above 80, so the filter drops it. Usually it belongs in the answer: keep each product whose running total before it was still under 80%.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.