Mid-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: L3
Asked in: Amazon

Problem

A headroom list that should hold every node running between 40 and 80 percent CPU inclusive came back short for the capacity team's holiday traffic freeze review. Rebuild it from infra_nodes, with every field of each qualifying node.

Not the heaviest. Not the lightest. The middle.

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 5 of 72 rows
node_id	hostname	region	node_type	cpu_pct	mem_pct	status
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
1444	web-01	eu-west-1	storage	56	28	running
1481	web-02	ap-south-1	gpu	69	47	stopped

Worked solution and explanation

This is a closed-interval filter dressed up as capacity planning. Everyone writes a range test on cpu_pct. What separates candidates is whether the word 'inclusive' survives into the predicate. The seed has nodes sitting at exactly 40.0 and exactly 80.0 CPU, so cpu_pct > 40 AND cpu_pct < 80 quietly drops them, and the headroom list comes back four nodes short of the 72 that qualify, the night before the freeze.

Walking to the answer

Step 1: Return the whole row from `infra_nodes`

'Every field' means all seven columns, node_id through status. Name them instead of writing SELECT * so the output shape stays pinned if someone adds a column to the table later.

Step 2: Keep both ends of the band

BETWEEN 40 AND 80 is inclusive on both sides by definition: it means exactly cpu_pct >= 40 AND cpu_pct <= 80. The nodes reading 40.0 and 80.0 are the boundary rows that the strict version loses, and they are the whole point of the problem.

Step 3: Let the missing readings fall out

28 nodes have no cpu_pct. A comparison against NULL is unknown rather than true, so WHERE discards those rows with no extra clause. That is the right call here, because a node with no reading cannot be placed inside the band.

Closed band on `cpu_pct`

SELECT node_id, hostname, region, node_type, cpu_pct, mem_pct, status
FROM infra_nodes
WHERE cpu_pct BETWEEN 40 AND 80

Strict bounds: cpu_pct > 40 AND cpu_pct < 80 returns 68 rows. The nodes at exactly 40.0 and 80.0 vanish with no error and no warning, and the list simply looks a little thin.
Closed band: cpu_pct BETWEEN 40 AND 80 returns all 72 rows, which is identical to cpu_pct >= 40 AND cpu_pct <= 80. Pick whichever reads clearer; the plan is the same.

Common follow-up questions

How would the query change if the band were 40 inclusive to 80 exclusive? (Tests translating boundary wording into matching comparison operators.)
Nodes with no `cpu_pct` reading drop out silently. How would you surface them separately so the capacity team still sees them? (Tests NULL awareness: an unknown reading is neither in nor out of the band.)
`status` holds both 'running' and 'Running'. How would you narrow the list to running nodes only? (Tests normalizing case before filtering on a dirty column.)