Nodes in Target 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: Vesta Innovations

Problem

A cross-region latency audit needs the full node record for every infrastructure node in 'us-east-1', 'us-west-2', or 'eu-west-1'.

The target regions need attention.

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 120 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
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

Worked solution and explanation

What this really is

This is a set-membership filter dressed up as a latency audit. infra_nodes spans six regions and the audit wants exactly three of them, so the whole problem is saying 'any of these three' as one predicate. Anyone can filter to one region. What separates candidates is writing the membership cleanly and then stopping: write region = 'us-east-1' and you hand back 40 of the 120 nodes, or notice the messy status values, add a filter nobody asked for, and drop the stopped and draining nodes the audit exists to inspect.

Step 1: Look at what `region` actually holds

The seed has six values: the three targets at 40 nodes each, plus 'ap-south-1', 'eu-central-1' and a single 'sa-east-1'. All lowercase, no padding. That tells you an exact match is safe, so no LOWER or TRIM belongs in this query.

Step 2: Say the three regions as one membership test

region IN ('us-east-1', 'us-west-2', 'eu-west-1') means the same as three equalities joined by OR, but it reads as the list it is and it stays correct when someone later appends AND status = 'running', where a bare chain of ORs would bind the AND to the last region only.

Step 3: Return the full record and filter nothing else

The ask is the full node record, so every column comes back. Region is the only condition: rows with a NULL cpu_pct or mem_pct stay, and 'STOPPED' next to 'stopped' in status is shown as stored, because the audit wants every node in those regions.

One membership filter, full record

SELECT *
FROM infra_nodes
WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1')

One region: WHERE region = 'us-east-1' runs, looks right and returns 40 rows. Every 'us-west-2' and 'eu-west-1' node is silently missing from the audit.
The three audit regions: WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1') returns all 120 nodes and leaves out exactly the 80 in the other three regions.

Common follow-up questions

Region codes start arriving as 'US-EAST-1' from a new provisioning tool. What changes? (Normalising one side of the comparison, and what that costs a partitioned `region` column.)
The audit list grows to forty regions kept in their own table. How does the query change? (Moving from a literal list to a membership test against another table's rows.)
Now produce the nodes outside those regions. What happens if `region` can be NULL? (Whether `NOT IN` keeps or drops NULL regions, and how to say which you want.)