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

  1. Total revenue per product in a CTE: SUM(quantity × price).
  2. Share of total: revenue ÷ SUM(revenue) OVER (), times 100.0.
  3. Cumulative share: a running SUM ordered by revenue descending, with ROWS UNBOUNDED PRECEDING, divided by the total.
  4. For "the first 80%": keep products whose revenue before them is still under 80% of the total.

Edge cases to mention

Common mistakes

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.