Cumulative Distribution and Percent of Total
Running percentages and share-of-total within partitions.
The Percent-of-Total Question
A staple reporting interview ask: "What percentage of total revenue does each category represent?" and its cumulative cousin "What is the running share of total?"
The trick is dividing each row's value by a window aggregate computed over the whole partition. Knowing you can put a grand total in a window function, no self-join needed, is the insight being tested.
Window SUM Without ORDER BY = Grand Total
Here is the key move: SUM(amount) OVER () with an empty OVER and no ORDER BY returns the total of the entire result set, repeated on every row.
Because there is no ORDER BY, there is no running frame, so the default frame is the whole partition. That total-on-every-row is exactly the denominator you need for a percent of total.
SELECT
category,
amount,
SUM(amount) OVER () AS grand_total
FROM category_sales;All lessons in this course
- Cumulative Sums With Window Frames
- ROWS vs RANGE Framing
- Moving Averages Over a Sliding Window
- Cumulative Distribution and Percent of Total