Which products make up 80% of revenue? (share of total)

Step 1 Solve it yourself first

Tables you will use

order_items products

What it really 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%".

Picture it

xychart-beta
  title "Cumulative share of revenue"
  x-axis ["Headphones", "Desk lamp", "Backpack", "Mug", "Notebook", "Pen"]
  y-axis "Cumulative %" 0 --> 100
  bar [55.5, 74.4, 89.5, 95.6, 98.7, 100]

The approach, step by step

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.

WITH product_revenue AS (
  SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_name
)
SELECT product_name, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total,
       ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING)
             / SUM(revenue) OVER (), 1) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC;

WITH product_revenue AS (
  SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_name
),
running AS (
  SELECT product_name, revenue,
         SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) - revenue AS revenue_before
  FROM product_revenue
)
SELECT product_name, revenue
FROM running
WHERE revenue_before < 0.8 * (SELECT SUM(revenue) FROM product_revenue)
ORDER BY revenue DESC;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  Mode SQL Tutorial — SQL Window Functions

  Mode, free

• Practice

  DataLemur — percentage of total questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the difference between the pct_of_total and cumulative_pct columns.

   Show answer

   pct_of_total is each product on its own: its revenue ÷ total revenue. The headphones bring in 440 of 793, so 55.5%. These add up to 100% across all rows.

   cumulative_pct is a running total: this product plus every product above it in the list, ÷ total revenue. It only grows as you go down: 55.5% after the headphones, 74.4% once the desk lamp is added, 89.5% after the backpack, and 100% at the last row. It answers "how much of revenue do the top N products bring in?", which is what the 80% question needs.

2. Writefrom a blank page, then run it

   Do the same analysis for customers: each customer's share of total spend and the cumulative share.

   Show answer

   Same structure, with customers instead of products: total each customer's spend, then divide by the grand total (empty OVER) and by the running total (ordered OVER). Priya alone is 53.6% of all spending, and Priya and Tom together are 88.3%.

   sqlCopy

   WITH customer_spend AS (
     SELECT c.name, SUM(o.amount) AS spend
     FROM orders o
     JOIN customers c ON c.customer_id = o.customer_id
     GROUP BY c.customer_id, c.name
   )
   SELECT name, spend,
          ROUND(100.0 * spend / SUM(spend) OVER (), 1) AS pct_of_total,
          ROUND(100.0 * SUM(spend) OVER (ORDER BY spend DESC ROWS UNBOUNDED PRECEDING)
                / SUM(spend) OVER (), 1) AS cumulative_pct
   FROM customer_spend
   ORDER BY spend DESC;

   Try it

   4 rows

   name	spend	pct_of_total	cumulative_pct
   Priya	425	53.6	53.6
   Tom	275	34.7	88.3
   Lena	60	7.6	95.8
   Zoe	33	4.2	100

3. Predictsay it before you run it

   Which products would be in "the first 90% of revenue"?

   Show answer

   Headphones, desk lamp, backpack and mug.

   The first three only reach 89.5%, just short of 90%, so you need the mug too (it takes the total to 95.6%). The rule in the alternative query is "keep a product if the revenue before it was still under the target"; with 0.9 instead of 0.8, the mug passes (89.5% before it) and the notebook doesn't (95.6% before it).

   sqlCopy

   WITH product_revenue AS (
     SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue
     FROM order_items oi
     JOIN products p ON p.product_id = oi.product_id
     GROUP BY p.product_name
   ),
   running AS (
     SELECT product_name, revenue,
            SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) - revenue AS revenue_before
     FROM product_revenue
   )
   SELECT product_name, revenue
   FROM running
   WHERE revenue_before < 0.9 * (SELECT SUM(revenue) FROM product_revenue)
   ORDER BY revenue DESC;

   Try it

   4 rows

   product_name	revenue
   Headphones	440
   Desk lamp	150
   Backpack	120
   Mug	48

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Make the phone case appear with 0 revenue and 0%, without breaking the cumulative column.

   Show answer

   Start from products with a LEFT JOIN, so the phone case has a row, and COALESCE its NULL revenue to 0. A zero adds nothing to any sum, so the totals and the running column are unchanged: the phone case sorts last, with 0% and a cumulative 100%.

   Add product_name to both ORDER BYs (the window's and the query's). That way two products with equal revenue, such as several unsold ones at 0, always come out in the same order, and the running total doesn't depend on chance.

   sqlCopy

   WITH product_revenue AS (
     SELECT p.product_name, COALESCE(SUM(oi.quantity * p.price), 0) AS revenue
     FROM products p
     LEFT JOIN order_items oi ON oi.product_id = p.product_id
     GROUP BY p.product_name
   )
   SELECT product_name, revenue,
          ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total,
          ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC, product_name ROWS UNBOUNDED PRECEDING)
                / SUM(revenue) OVER (), 1) AS cumulative_pct
   FROM product_revenue
   ORDER BY revenue DESC, product_name;

   Try it

   7 rows

   product_name	revenue	pct_of_total	cumulative_pct
   Headphones	440	55.5	55.5
   Desk lamp	150	18.9	74.4
   Backpack	120	15.1	89.5
   Mug	48	6.1	95.6
   Notebook	25	3.2	98.7
   Pen	10	1.3	100
   Phone case	0	0	100

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I total revenue per product in a CTE, then divide each by SUM over an empty window for the share of total, and use a running SUM ordered by revenue descending with an explicit ROWS frame for the cumulative share. To list the products making up the first 80%, I keep those whose revenue before them is still under 80% of the total, which includes the product that crosses the line.

Follow-up questions

Tick each check when you can do it without notes