The Footprint

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

Problem

Capacity planning is reviewing how the server fleet spreads across regions. Show the node count for each region.

Some regions carry more of the fleet than others. Show how it is spread.

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	node_count
ap-south-1	39
eu-central-1	40
eu-west-1	40
sa-east-1	1
us-east-1	40

Worked solution and explanation

What this is really testing

Underneath the capacity-planning framing this is a plain group-and-tally: one row per region, COUNT(*) as the metric, sorted so the heaviest region floats to the top. Anyone can write the GROUP BY. What separates candidates is making the sort deterministic. When two regions carry the same node count, a bare ORDER BY node_count DESC leaves their relative order to the engine, so the row you call 'first' can shift between runs. Add a tiebreaker and the output is stable.

Building it

Step 1: Collapse to one row per region

GROUP BY region collapses the 10,000 node rows into one row per region. Every non-aggregated column in the SELECT has to be the grouping key, which is why only region and the aggregate appear.

Step 2: Count every node

COUNT(*) tallies every node in each region, including stopped, draining, and offline ones. The prompt asks for the fleet's spread, not just the live nodes, so there is no status filter. If it asked for active nodes only, you would move that condition into a WHERE clause before grouping.

Step 3: Sort heaviest first, deterministically

ORDER BY node_count DESC puts the densest region first. Append region as a secondary sort so regions tied on count come back in a fixed order rather than an arbitrary one.

The solution

Node count per region, densest first

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

One scan, one grouped aggregate, one sort.

A single fact table; region is the grouping dimension.

infra_nodes: node_id int PK, hostname text, region text, node_type text, cpu_pct float, mem_pct float, status text

Common follow-up questions

How would you change the query to count only nodes whose status is running, given the status column has mixed casing like 'Running' and 'STOPPED'? (Tests whether the candidate can pivot from all-nodes to a filtered metric using WHERE before the aggregate.)
If capacity planning wanted the single densest region within each `node_type` instead, how would your approach change? (Tests knowledge of moving from ORDER BY plus LIMIT to a windowed or correlated approach when only the leader is wanted per some partition.)
If some rows had a NULL region, how would they appear in your result, and would you keep or exclude them? (Tests handling of NULL grouping keys and whether the candidate accounts for them explicitly.)