Calculate each region’s percentage of total sales
Return each region, its total sales, and its percentage of all sales rounded to two decimals. Amounts and regions are non-NULL; return NULL percentages when the grand total is zero. Return only the requested columns and follow the specified ordering. This question probes aggregate first and use a window grand total; 100.0 avoids postgresql integer division while nullif makes zero totals safe.
Input
Setup script defining sales(id INT, region VARCHAR(20), quarter INT, amount INT).
Output
region, region_total, percentage; ascending region.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE sales(id INT, region VARCHAR(20), quarter INT, amount INT); INSERT INTO sales VALUES (1,'East',1,100),(2,'East',1,50),(3,'East',3,200),(4,'West',2,80);
Expected result
region | region_total | percentage East | 350 | 81.4 West | 80 | 18.6
Another dataset (1)
CREATE TABLE sales(id INT, region VARCHAR(20), quarter INT, amount INT); INSERT INTO sales VALUES (1,'Only',4,0),(2,'Only',4,30);
Expected result
region | region_total | percentage Only | 30 | 100.0
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Compute totals per region first.
- Divide by a window SUM and protect a zero denominator.
Target complexity: time O(n) · space O(n)