Pareto 80-20 cumulative analysis

Pareto 80-20 analysis ranks items from largest contributor to smallest and tracks a running total until you hit 80% of the whole - revealing the small group responsible for most of the result. Like scanning a playlist sorted by most-played: you stop once the top few songs account for 80% of all listens, because those are the ones worth your attention.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Pareto analysis - finding the 80%

SELECT product_id, SUM(price) AS total, ROUND(100.0 * SUM(SUM(price)) OVER (ORDER BY SUM(price) DESC) / SUM(SUM(price)) OVER (), 1) AS cum_pct FROM products GROUP BY product_id ORDER BY total DESC

Result:

product_id	total	cum_pct
P2001	4890	46.7
P2002	3215	77.4
P2003	1240	89.3
P2004	890	97.7
P2005	320	100.0

Step by step

Run the query without SUM so you can see what the unfiltered values look like.

SELECT product_id, price, ROUND(100.0 * SUM(SUM(price)) OVER (ORDER BY SUM(price) DESC) / SUM(SUM(price)) OVER (), 1) AS cum_pct FROM products GROUP BY product_id ORDER BY total DESC;

SUM totals the numbers in a column. Works on numeric columns only.

SELECT product_id, SUM(price), ROUND(100.0 * SUM(SUM(price)) OVER (ORDER BY SUM(price) DESC) / SUM(SUM(price)) OVER (), 1) AS cum_pct FROM products GROUP BY product_id ORDER BY total DESC;

AS gives the SUM result a friendly column name so the output reads cleanly.

SELECT product_id, SUM(price) AS total, ROUND(100.0 * SUM(SUM(price)) OVER (ORDER BY SUM(price) DESC) / SUM(SUM(price)) OVER (), 1) AS cum_pct FROM products GROUP BY product_id ORDER BY total DESC;

Practice

Try each one in your head first, then open the answer.

1. On orders, compute each order_id's share of total revenue plus the running cumulative percent - for 80-20 / Pareto analysis.

SELECT order_id, SUM(revenue) AS total, ROUND(100.0 * SUM(SUM(revenue)) OVER (ORDER BY SUM(revenue) DESC) / SUM(SUM(revenue)) OVER (), 1) AS cum_pct FROM orders GROUP BY order_id ORDER BY total DESC

2. On store_sales, compute each sale_id's share of total revenue plus the running cumulative percent - for 80-20 / Pareto analysis.

SELECT sale_id, SUM(revenue) AS total, ROUND(100.0 * SUM(SUM(revenue)) OVER (ORDER BY SUM(revenue) DESC) / SUM(SUM(revenue)) OVER (), 1) AS cum_pct FROM store_sales GROUP BY sale_id ORDER BY total DESC

3. On products, compute each product_id's share of total price plus the running cumulative percent - for 80-20 / Pareto analysis.

SELECT product_id, SUM(price) AS total, ROUND(100.0 * SUM(SUM(price)) OVER (ORDER BY SUM(price) DESC) / SUM(SUM(price)) OVER (), 1) AS cum_pct FROM products GROUP BY product_id ORDER BY total DESC

4. On employees, compute each employee_id's share of total salary plus the running cumulative percent - for 80-20 / Pareto analysis.

SELECT employee_id, SUM(salary) AS total, ROUND(100.0 * SUM(SUM(salary)) OVER (ORDER BY SUM(salary) DESC) / SUM(SUM(salary)) OVER (), 1) AS cum_pct FROM employees GROUP BY employee_id ORDER BY total DESC

5. On reviews, compute each review_id's share of total rating plus the running cumulative percent - for 80-20 / Pareto analysis.

SELECT review_id, SUM(rating) AS total, ROUND(100.0 * SUM(SUM(rating)) OVER (ORDER BY SUM(rating) DESC) / SUM(SUM(rating)) OVER (), 1) AS cum_pct FROM reviews GROUP BY review_id ORDER BY total DESC

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed