Top Regions by High CPU Nodes

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: L5
Asked in: Yelp

Problem

We only have budget to add hardware in three regions this quarter, and a region's load is the total CPU percentage across all of its nodes in infra_nodes. Which three regions carry the heaviest load, heaviest first?

Five regions with the hottest CPUs.

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	total_cpu_pct
us-east-1	1770
ap-south-1	1707
eu-west-1	1664

Worked solution and explanation

What this really is

This is a top-K over a grouped total wearing a capacity-planning badge. The fleet spans six regions and the budget covers three, so the whole question is: build one load figure per region, sort those figures, and cut at three. Everyone writes the SUM. What separates candidates is the order of operations: the cut has to land after the totals exist and after they are sorted. Cut early, or sort by something other than the total, and the hardware ships to the wrong regions.

Build it in order

Step 1: Collapse nodes to regions

GROUP BY region with SUM(cpu_pct) turns two hundred node rows into one row per region. Load is a quantity, so it is summed; a node count would reward a region stuffed with idle boxes.

Step 2: Sort the totals, heaviest first

ORDER BY total_cpu_pct DESC sorts on the alias, so the expression is written once. On this data the six totals are all different, so the order is fully determined and two correct answers cannot disagree.

Step 3: Keep three

LIMIT 3 runs last, after grouping and sorting. That is exactly why it is safe: it trims a sorted list of region totals, never raw rows. Drop it and you hand back all six regions for a three-region budget.

Three heaviest regions from `infra_nodes`

SELECT
    region,
    SUM(cpu_pct) AS total_cpu_pct
FROM infra_nodes
GROUP BY region
ORDER BY total_cpu_pct DESC
LIMIT 3

Group, sort on the total_cpu_pct alias, then cut with LIMIT 3.

Common follow-up questions

What if two regions tied for third place on `total_cpu_pct`? (Tests whether the candidate sees that a bare `LIMIT 3` drops one arbitrarily and reaches for a tiebreaker or a tie-inclusive cut.)
How would you rank regions by average load per measured node instead of total load? (Tests the switch to `AVG(cpu_pct)` and that its denominator already excludes `NULL` readings.)
Should stopped or offline nodes count toward a region's load? (Tests reading the `status` column and noticing its inconsistent casing before filtering on it.)