Compute Nodes in Key Regions

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: Forbes

Problem

Which nodes in infra_nodes run in one of the European regions, 'eu-west-1' or 'eu-central-1', and are working above 70% CPU? Bring back each of those nodes' full record.

Compute nodes across the key regions.

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 18 rows
node_id	hostname	region	node_type	cpu_pct	mem_pct	status
1814	WEB-03	eu-west-1	storage	86	18	Running
2073	db-replica	eu-central-1	general	77	51	STOPPED
2369	web-02	eu-west-1	storage	81	3	stopped
2628	worker-02	eu-central-1	general	72	36	draining
2924	db-primary	eu-west-1	storage	76	88	Running

Worked solution and explanation

What this is really asking

This is a two-condition filter where one condition is a menu of allowed values. Nobody misses the CPU line. What separates candidates is whether the region alternatives stay scoped under it. Write the regions as a loose OR beside the CPU test and precedence quietly hands every idle node in one region to the hot list.

Step 1: Draw the CPU line

cpu_pct > 70 keeps only the nodes working hard. Rows with no reading are NULL, and a NULL is never greater than 70, so they fall out without any extra guard.

Step 2: Express the region set as one condition

region IN ('eu-west-1', 'eu-central-1') is a single predicate that is true for either region. Because it is one predicate, the AND binds it to the CPU check as a unit, which is exactly the meaning the question wants.

Step 3: Return the whole record

The question asks for each node's full record, so SELECT * matches the expected output column for column.

Hot nodes in the European regions

SELECT *
FROM infra_nodes
WHERE cpu_pct > 70
  AND region IN ('eu-west-1', 'eu-central-1')

The precedence bug

SELECT *
FROM infra_nodes
WHERE cpu_pct > 70
  AND region = 'eu-west-1'
   OR region = 'eu-central-1'

AND binds tighter than OR: every node in 'eu-central-1' comes back, whatever its CPU.

Bare OR: Parsed as (cpu_pct > 70 AND region = 'eu-west-1') OR region = 'eu-central-1'. The second region escapes the CPU check.
IN list: region IN ('eu-west-1', 'eu-central-1') is one predicate, so the CPU check applies to both regions. Parenthesizing the OR is the same fix.

Common follow-up questions

What if the region list came from a `regions` reference table instead of being typed in? (Tests swapping a literal `IN` list for a subquery or a join.)
How would you count hot nodes per European region instead of listing them? (Same filter, then `GROUP BY region` with `COUNT(*)`.)
What changes if a node missing a `cpu_pct` reading should be treated as hot until proven otherwise? (Tests handling NULL explicitly with `cpu_pct IS NULL OR cpu_pct > 70`, parenthesized.)