SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…