Region Share of Total Revenue

Given a revenue log with several rows per region, report each region’s total revenue and what percentage of all revenue it represents.

Schema

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  region   TEXT NOT NULL,
  amount   NUMERIC NOT NULL
);

Sample data:

Task

For each region, return:

• region
• region_total — the sum of amount for that region
• pct_of_total — the region total as a percentage of the grand total across all regions, rounded to 2 decimal places

Order the result by pct_of_total descending, then region ascending.

Expected output

Approach

Group by region to get each region’s total with SUM(amount). The grand total is the sum of those per-region totals, which a window function computes over the already-grouped rows: SUM(SUM(amount)) OVER (). The inner SUM is the group aggregate; the outer SUM(...) OVER () runs across all groups with an empty window, giving one grand total attached to every row. Dividing one by the other yields the share.

Query

SELECT
  region,
  SUM(amount) AS region_total,
  ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) AS pct_of_total
FROM orders
GROUP BY region
ORDER BY pct_of_total DESC, region ASC;

Walkthrough

• Grouping gives region totals: North = 100 + 200 = 300, South = 300, West = 400.
• SUM(SUM(amount)) OVER () adds those totals: 300 + 300 + 400 = 1000, the grand total, repeated on every row.
• Percentages: West = 100.0 * 400 / 1000 = 40.00, North = 100.0 * 300 / 1000 = 30.00, South = 30.00.
• ORDER BY pct_of_total DESC puts West (40) first; North and South tie at 30, so region ASC breaks the tie as North then South.

Complexity & notes

• The window aggregate runs after grouping and needs no join or subquery, so it is a single grouped pass plus a lightweight window scan over the small grouped set.
• Order of operations matters: window functions execute after GROUP BY, which is why SUM(SUM(amount)) OVER () is legal — the outer SUM sees grouped rows, not raw rows.
• Use 100.0 rather than 100: with integer inputs, 100 * x / total can truncate mid-expression in some engines. Forcing numeric arithmetic avoids that; casting like SUM(amount)::numeric is another way.
• An alternative without window functions is a scalar subquery for the grand total: ... / (SELECT SUM(amount) FROM orders). It is portable but scans the table twice.
• Guard against a zero grand total (empty or all-zero data) if that is possible, since it would divide by zero.