Interview Drills · SUM() OVER () · percent of total
30. Share of total spend
Concept: SUM() OVER () · percent of totalshow
An empty window, OVER (), spans the whole result, so SUM(x) OVER () puts the grand total on every row. After a GROUP BY you can even wrap an aggregate: SUM(SUM(x)) OVER () is the total of all the group sums.
Example (different data):
SELECT region, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 1) AS pct FROM orders GROUP BY region;
Your task
The product team wants to know which spending categories matter most, as a share of all card spend.
Return category, total (sum of amount) and pct_of_total (that total as a percentage of all spend, rounded to 1 decimal), largest share first.
Starting Postgres in your browser…
Checking your access…