Percentage of Total Sales SQL Query | SQL Interview Question

Problem statement

Prompt

Given the sales table below, write a query that returns, for each region, the percentage of overall sales that region contributes.

Output columns: region and pct_of_total, rounded to 2 decimal places.
Order the result by pct_of_total descending, then by region.
sales.amount may be NULL; NULL amounts are ignored by SUM.

Example

id	region	amount
1	North	100
2	North	50
3	South	50

Total = 200. Expected result:

region	pct_of_total
North	75
South	25

Constraints

sales.id is the primary key.
sales.amount is nullable.

Tips

Aggregate to per-region totals, then use SUM(region_total) OVER () to broadcast the grand total to every row.
amount * 100.0 / grand_total keeps the division in floating point so rounding behaves.

Schema

CREATE TABLE sales (
    id     INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    amount INTEGER  -- NULL allowed
);

Sample data

INSERT INTO sales (id, region, amount) VALUES
  (1, 'North', 100),
  (2, 'North', 50),
  (3, 'South', 50),
  (4, 'East',  100),
  (5, 'East',  50),
  (6, 'West',  50);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: each region's percentage of the overall total.
WITH region_totals AS (
    SELECT region, SUM(amount) AS region_total
    FROM sales
    WHERE amount IS NOT NULL
    GROUP BY region
)
SELECT region,
       ROUND(region_total * 100.0 / SUM(region_total) OVER (), 2) AS pct_of_total
FROM region_totals
ORDER BY pct_of_total DESC, region;

Key concepts

SUM OVER ()
Window aggregation
ROUND

Percentage of Total Sales