Min and Max Sale Per Region — SQL

Solving tips

MIN and MAX are ordinary aggregates — combine several of them in one GROUP BY pass.
You can compute a derived value like MAX(x) - MIN(x) directly in SELECT without a subquery.
Add a stable tie-break column to ORDER BY when the primary sort key has duplicates.

Given a sales log, report the smallest and largest sale amount in each region and the spread between them.

Schema

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region  TEXT NOT NULL,
  amount  NUMERIC NOT NULL
);

Sample data:

sale_id	region	amount
1	North	100
2	North	250
3	North	175
4	South	400
5	South	150
6	West	300

Task

For each region, return:

region
min_amount — the smallest amount in that region
max_amount — the largest amount in that region
spread — max_amount minus min_amount

Order the result by spread descending, then by region ascending as a tie-break.

Expected output

region	min_amount	max_amount	spread
South	150	400	250
North	100	250	150
West	300	300	0

Approach

Group by region and apply MIN(amount) and MAX(amount) in the same pass. The spread is just MAX(amount) - MIN(amount) computed inline; there is no need for a self-join or subquery. The two-level ORDER BY handles ties.

Query

SELECT
  region,
  MIN(amount)               AS min_amount,
  MAX(amount)               AS max_amount,
  MAX(amount) - MIN(amount) AS spread
FROM sales
GROUP BY region
ORDER BY spread DESC, region ASC;

Walkthrough

North (ids 1, 2, 3): min 100, max 250, spread 150.
South (ids 4, 5): min 150, max 400, spread 250.
West (id 6): a single row, so min and max are both 300 and spread is 0.
ORDER BY spread DESC puts South (250) first, then North (150), then West (0). No tie on spread here, but region ASC is there to make the order deterministic if two spreads matched.

Complexity & notes

Single aggregate pass, O(n). MIN/MAX need no ordering of the input.
MIN and MAX ignore NULLs like other aggregates; a group of all-NULL amounts would return NULL, and NULL - NULL is NULL. Here amount is NOT NULL, so that case cannot arise.
Referencing the alias spread in ORDER BY is fine, but note you cannot reuse it inside the SELECT list of the same query level — that is why spread repeats the MAX - MIN expression rather than referencing the alias.
Without the region tie-break, rows sharing a spread could come back in any order across runs.