How does the cumulative percentage (pareto) query work?

The CTE gives one row per product with its total sales. The running SUM in descending order adds the biggest products first. Dividing by the grand total, SUM(sales) OVER (), turns the running sum into a share; the row where it first passes 0.8 marks the 80% point.

What commonly goes wrong with cumulative percentage (pareto) in SQL?

The share must be computed on the same set that is ranked; filter before the CTE, not after.

Cumulative Percentage (Pareto) in SQL

A cumulative percentage ranks items from largest to smallest and shows how much of the total the top items add up to, as in a Pareto or 80/20 analysis.

Example query

This example assumes: order_items(id, product_id bigint, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH by_product AS (
  SELECT product_id, SUM(total_amount) AS sales
  FROM order_items
  GROUP BY product_id
)
SELECT product_id,
       sales,
       SUM(sales) OVER (
         ORDER BY sales DESC, product_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(sales) OVER () AS cumulative_share
FROM by_product
ORDER BY sales DESC, product_id;

How it works

The CTE gives one row per product with its total sales.
The running SUM in descending order adds the biggest products first.
Dividing by the grand total, SUM(sales) OVER (), turns the running sum into a share; the row where it first passes 0.8 marks the 80% point.

MySQL, BigQuery, SQL Server and SQLite differences

Works unchanged in PostgreSQL, MySQL 8+, SQL Server, BigQuery and SQLite 3.25+.

Common mistakes

The share must be computed on the same set that is ranked; filter before the CTE, not after.
Negative values (returns, credits) make the running share non-monotonic; decide whether to net them or exclude them.
Whether 20% of products really make 80% of sales is a result to check, not an assumption.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Which products make up the first 80% of our sales?
Show cumulative revenue share by customer, largest first.