Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which products make up the top 80% of revenue?

Which products make up the top 80% of revenue? Total each product, sort biggest first, then work out a running share of the grand total: SUM OVER (ORDER BY revenue DESC) divided by SUM OVER (). Keep the products whose running share is 0.8 or less.

Example

Pareto: biggest first, a running share, then a cut-off

WITH t AS (
  SELECT product, SUM(revenue) AS revenue
  FROM orders
  GROUP BY product
), r AS (
  SELECT product,
         revenue,
         1.0 * SUM(revenue) OVER (ORDER BY revenue DESC)
           / SUM(revenue) OVER () AS running_share
  FROM t
)
SELECT product, revenue, running_share
FROM r
WHERE running_share <= 0.8

Result:

Step by step

1. Biggest first.

   WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT product, revenue, running_share FROM r WHERE running_share <= 0.8

2. No join.

   WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT product, revenue, running_share FROM r WHERE running_share <= 0.8

3. WHERE running_share <= 0.8, after it's worked out.

   WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT product, revenue, running_share FROM r WHERE running_share <= 0.8

4. Two CTEs: totals, then the running share.

   WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT product, revenue, running_share FROM r WHERE running_share <= 0.8

Practice

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

WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT product, revenue, running_share FROM r WHERE running_share <= 0.8

WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product) SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t ORDER BY revenue DESC

WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product) SELECT product, 1.0 * revenue / SUM(revenue) OVER () AS share FROM t ORDER BY revenue DESC

WITH t AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id), r AS (SELECT customer_id, spend, 1.0 * SUM(spend) OVER (ORDER BY spend DESC) / SUM(spend) OVER () AS running_share FROM t) SELECT customer_id, spend, running_share FROM r WHERE running_share <= 0.8

WITH t AS (SELECT product, SUM(revenue) AS revenue FROM orders GROUP BY product), r AS (SELECT product, revenue, 1.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () AS running_share FROM t) SELECT COUNT(*) AS products FROM r WHERE running_share <= 0.8