Analytics Patterns · SUM() OVER (PARTITION BY) · percent of group
48. Customer share of country revenue
Concept: SUM() OVER (PARTITION BY) · percent of groupshow
To get each row's share of its own group, divide by a window total partitioned by that group. After a GROUP BY, SUM(SUM(x)) OVER (PARTITION BY g) adds up the group sums within g only, so the shares inside each g add to 100.
Example (different data):
SELECT team, player, SUM(points) AS points, SUM(SUM(points)) OVER (PARTITION BY team) AS team_points FROM box_scores GROUP BY team, player;
Your task
Country managers want to know how dependent they are on their biggest customers.
Using completed orders, return country, name, revenue (that customer's completed total) and pct_of_country (revenue as a percentage of their country's completed revenue, rounded to 1 decimal). Sort by country, then pct_of_country highest first.
Starting Postgres in your browser…
Checking your access…