Where the Fleet Lives

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

Problem

The capacity planning team is auditing how nodes are distributed across six key regions: us-east-1, us-west-2, eu-west-1, eu-central-1, ap-southeast-1, and ap-northeast-1. Break the node count down by region, busiest first.

Six regions on the map. Where does the fleet cluster?

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 4 of 4 rows
region	node_count
eu-central-1	40
eu-west-1	40
us-east-1	40
us-west-2	40

Worked solution and explanation

What this really is

This is a filtered per-group count wearing a capacity-audit costume. The skill being probed: can you count nodes within a fixed whitelist of regions without dragging in the regions nobody asked about, and without expecting a row for a region that has no nodes? Anyone can write a count. The move that separates candidates is putting the region filter and the region grouping in the same query and knowing exactly which rows survive each. Forget the filter and you report all twelve regions; assume the grouping hands back all six and you will chase a phantom row for a region the fleet never deployed to.

The two moves

Step 1: Fence off the six regions

WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1', 'eu-central-1', 'ap-southeast-1', 'ap-northeast-1') is evaluated first, before any grouping. Everything downstream only ever sees rows from those six regions, so the audit stays scoped no matter how many other regions live in the table.

Step 2: Count per surviving region

GROUP BY region collapses the filtered rows into one bucket per region, and COUNT(*) tallies every node in each bucket. COUNT(*) counts rows, so a node with a null cpu or mem still counts, which is exactly what you want for a headcount.

The solution

Filter, group, count, order

SELECT region, COUNT(*) AS node_count
FROM infra_nodes
WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1', 'eu-central-1', 'ap-southeast-1', 'ap-northeast-1')
GROUP BY region
ORDER BY node_count DESC, region

The IN-list scopes the scan; GROUP BY buckets by region; the tie-break keeps the ordering deterministic.

Filter then count: WHERE region IN (...) scopes the scan up front, so grouping and counting only ever touch the six regions. One pass, nothing aggregated that you throw away.
Count then discard: Grouping all twelve regions and dropping the unwanted ones in application code aggregates rows you will discard and moves the filter out of the engine. More work, and easier to get wrong.

Common follow-up questions

How would you return a row for every one of the six regions, showing zero for any region with no nodes? (Tests whether they reach for a region reference list and an outer join instead of a plain grouped count.)
Two regions tie on node count. How do you make the ordering stable and repeatable? (Tests tie-breaking with a deterministic secondary sort key.)
The status column mixes 'running', 'Running', and 'STOPPED'. If the audit wanted only running nodes, how would you filter reliably? (Tests case-insensitive filtering on messy categorical data.)