All Infra 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: Airbnb

Problem

Before provisioning a new availability zone, the infrastructure team needs to know which regions already have node presence. Pull a deduplicated list of every region currently represented in the node inventory, arranged alphabetically so the team can scan it top to bottom.

The infrastructure spans the globe. Map it.

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 6 rows
region
ap-south-1
eu-central-1
eu-west-1
sa-east-1
us-east-1

Worked solution and explanation

What this really is

This is a grain question dressed up as an inventory request. infra_nodes holds one row per node, and region is an attribute repeated on every one of them: 200 rows, six regions. Everyone can project the column. What separates candidates is noticing that the team asked for one row per region, not one row per node. Miss that and you hand them 200 rows with us-west-2 repeated dozens of times and 'sa-east-1', which sits on exactly one node, buried somewhere in the middle.

Getting there

Step 1: Name the grain before touching the table

The ask is one row per region with presence. The table is one row per node. Saying that mismatch out loud is the whole problem; everything after it is mechanical.

Step 2: Collapse repeats of `region`

SELECT DISTINCT region keeps one row per value. Nothing needs cleaning first: unlike status and node_type, every region value has a single spelling, so no zone survives as two separate entries.

Step 3: Sort for a top-to-bottom scan

ORDER BY region gives the alphabetical list the team reads. Once the values are deduplicated each one is unique, so the order is total and two correct answers can never disagree on it.

One row per region, alphabetical

SELECT DISTINCT region
FROM infra_nodes
ORDER BY region

Row-level projection: SELECT region FROM infra_nodes returns 200 rows, one per node, so each zone repeats as many times as it has nodes.
One row per region: SELECT DISTINCT region returns six rows, including 'sa-east-1', which a single node accounts for.

Common follow-up questions

What changes if `region` arrives as 'US-East-1' on some rows and ' us-east-1' on others? (Tests normalizing with `LOWER` and `TRIM` before collapsing repeats.)
How would you list only regions with at least one running node, given that `status` mixes 'running' and 'Running'? (Tests filtering on a dirty column before deduplicating.)
The team now wants each region's node count alongside it. What changes in the query and in the output shape? (Tests moving from a set of values to a grouped aggregate.)
At a billion nodes, would you still derive regions from `infra_nodes`, or keep a regions reference table? (Tests when a small dimension table beats scanning facts.)