Nodes by Region and Type

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: medium
Seniority: L3
Asked in: Apple

Problem

The capacity team is mapping fleet composition and needs node counts broken down by region and node type, listed alphabetically by region.

Broken down by region. Broken down by type.

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 7 rows
region	node_type	node_count
ap-south-1	gpu	39
eu-central-1	general	40
eu-west-1	Storage	1
eu-west-1	storage	39
us-east-1	compute	40

Worked solution and explanation

What this is really asking

Strip the capacity-planning costume and this is a choice of grain: one count per (region, node_type) pair in infra_nodes. The trap is that the two columns almost move together on this data, roughly 40 nodes of one type per region, so a count keyed on either column alone looks right on five rows and quietly loses the two that are not. Everyone gets COUNT(*) right. What separates candidates is keying on the full pair and then pinning the alphabetical order the prompt asks for, because SQL hands back groups in no promised order.

Build it in three moves

Step 1: Group on the full grain

List both dimensions: GROUP BY region, node_type. On this seed that turns 200 rows into seven, one per pair that actually occurs. Key on region alone and eu-west-1's two stored values ('storage' and 'Storage') fold into one row. Key on node_type alone and the single 'sa-east-1' gpu node vanishes into one gpu total of 40. Neither mistake raises an error.

Step 2: Count every row

Use COUNT(*). Every node counts whatever its status, so there is no filter, and the NULLs in this table live in cpu_pct and mem_pct, which the count never reads. Counting rows is the simplest correct choice.

Step 3: Pin the ordering

Add ORDER BY region so the alphabetical requirement is enforced by the query, not left to chance. Only region order is promised: the two eu-west-1 rows may come back in either order.

The solution

Multi-column GROUP BY with explicit ordering

SELECT region, node_type, COUNT(*) AS node_count
FROM infra_nodes
GROUP BY region, node_type
ORDER BY region

One row per region-and-type pair, ordered alphabetically by region.

Common follow-up questions

How would you fold 'Storage' into 'storage' so eu-west-1 shows a single row of 40, and where should that cleaning live? (Tests whether normalization belongs in the report query or upstream in the inventory feed.)
If `node_type` contained NULLs, how would the output change, and would `COUNT(*)` still be the right choice? (Tests understanding that `COUNT(*)` counts every row while `COUNT(col)` skips NULLs.)
How would you also return each pair's share of its region's total, highest share first? (Tests extending a two-dimensional tally with a per-region denominator and a richer sort.)
At hundreds of millions of rows, what index would let the planner avoid both the scan cost and the final sort? (Tests indexing intuition for grouped aggregation at scale.)