Hottest Regions by CPU

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L4
Asked in: Visa

Problem

The infra_nodes table holds one row per server node with its region and CPU utilization, and some nodes never reported a CPU reading. Which three regions run hottest on average across the nodes that did report, hottest first?

Where the fleet runs warmest.

Tables

infra_nodes

Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT

infra_nodes: 10 of 200 rows
node_id	hostname	region	node_type	cpu_pct	mem_pct	status
1037	web-02	us-west-2	memory	13	19	stopped
1074	api-01	eu-west-1	storage	26	38	draining
1111	api-02	sa-east-1	gpu	39	57	offline
1148	db-primary	eu-central-1	general	52	76	Running
1185	db-replica	us-east-1	compute	65	95	STOPPED
1222	cache-01	us-west-2	memory	78	14	running
1259	worker-01	eu-west-1	Storage	NULL	NULL	stopped
1296	worker-02	ap-south-1	gpu	4	52	draining
1333	gpu-node-01	eu-central-1	general	17	71	offline
1370	WEB-03	us-east-1	compute	30	90	Running

Expected output

First 3 of 3 rows
region	avg_cpu
ap-south-1	51.73
us-east-1	49.17
eu-west-1	48.94

Worked solution and explanation

This is a rank-the-groups question dressed up as capacity planning. Every row in infra_nodes is a node, but the thing being ranked is a region, and a region's heat is stored nowhere: it only comes into being once you collapse each region's nodes into one average. The trap is sorting nodes instead of regions. ORDER BY cpu_pct DESC LIMIT 3 hands back nodes, and even the regions behind the hottest nodes ('ap-south-1', 'us-west-2', 'eu-central-1') are the wrong answer: two of the three regions that actually run hottest never show up.

Getting there

Step 1: Collapse nodes into one row per `region`

The answer has one row per region, so region is the grain. GROUP BY region turns 200 node rows into six region rows, and AVG(cpu_pct) gives each one its sustained load. Until that number exists, the ranking has nothing to stand on.

Step 2: Let missing readings drop out

28 nodes have a NULL cpu_pct. AVG skips them, and that is the right call: a node that never reported tells you nothing about heat. Wrap the column in COALESCE(cpu_pct, 0) and 'ap-south-1' falls from 51.73 to 43.77, handing first place to 'us-east-1' at 44.25.

Step 3: Sort the averages, keep three

ORDER BY avg_cpu DESC LIMIT 3 sorts the six region averages and keeps the top. Sorting by the alias means the expression is written once. ROUND(..., 2) matches the two decimals in the preview, and the averages sit far enough apart that rounding never creates a tie at the cut.

Hottest three regions

SELECT region, ROUND(AVG(cpu_pct),2) AS avg_cpu FROM infra_nodes GROUP BY region ORDER BY avg_cpu DESC LIMIT 3

One pass, one row per region, top three by average.

The node-level shortcut

SELECT region, cpu_pct
FROM infra_nodes
ORDER BY cpu_pct DESC
LIMIT 3

Returns nodes, not regions: the two 99 readings in 'ap-south-1' and a 98 in 'us-west-2'.

Sort nodes: ORDER BY cpu_pct DESC LIMIT 3 ranks single readings. The regions behind the hottest nodes are 'ap-south-1', 'us-west-2' and 'eu-central-1': one spiky box is enough to get a region in.
Sort regions: GROUP BY region first, then sort on AVG(cpu_pct). The answer is 'ap-south-1', 'us-east-1' and 'eu-west-1', the regions whose nodes run hot across the board.

Common follow-up questions

Only rank regions with at least five reporting nodes. Where does that condition go? (Filtering groups on an aggregate such as `COUNT(cpu_pct)` instead of filtering rows.)
'sa-east-1' has a single node. Should one reading count as a region average? (Sample size behind an average, and how to surface it next to the number.)
What if two regions tie for third place? (Tie handling at a `LIMIT` cut.)
Break the same answer down by `node_type` within each region. (A two-key grain, plus the 'Storage' versus 'storage' case variant in `node_type`.)