The Products Behind 80% of Revenue

An interviewer asks: which products bring in 80% of the revenue? In most shops a few products earn most of the money (the Pareto rule). To find them, take the products biggest first, keep a running total of their revenue, and stop once it passes 80% of the grand total.

Example: both totals are window sums. SUM(x) OVER (ORDER BY …) adds up row by row in that order, and SUM(x) OVER (), with empty brackets, puts the sum of all the rows on every row. On a table of daily sales:

SELECT day, sales,
    SUM(sales) OVER (ORDER BY day) AS so_far,
    SUM(sales) OVER () AS whole_week
FROM daily_sales

Be careful where you stop. A product is needed while the products before it made less than 80% of the grand total, and what they made is the running total minus the product's own revenue. So the product that crosses 80% still makes the list. Keeping only the rows whose running total is at most 80% would stop one product short.

The share is the running total times 100.0, divided by the grand total. Write 100.0, not 100, so the whole numbers keep their decimals.

The product_revenue named query is already in your editor: revenue per product for the orders that weren't cancelled, 266 in all. Your query must print:

name | revenue | share
Mug | 84 | 31.6
Lamp | 58 | 53.4
Teapot | 44 | 69.9
Notebook | 35 | 83.1
(4 rows)

Your task: finish the running named query with running_total (biggest revenue first) and grand_total. Then select from running each product's name, revenue and running share in percent, rounded to 1 decimal and named share. Keep the products up to the one that passes 80%, biggest revenue first.

Assignment
Follow the Coding Tutorial to add the two window sums to running and write the main query, one clause per line.

Hint
The window's ORDER BY only sets the order of adding up; the list needs its own ORDER BY at the end. To test a product, look at what came before it, not at its own running total.