The Well-Defended Borders

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: Burtch Works

Problem

Each row of infra_nodes is one node with its region and a status that different producers spell inconsistently ('running', 'Running'), though every spelling of running means the node is up. A region needs at least 14 running nodes to meet the minimum cluster size, so list the regions that meet it along with how many running nodes each has.

Some frontiers already stand ready. Find the ones that do.

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

Worked solution and explanation

What this really is

Strip off the minimum-cluster-size costume and this is a per-region count of rows that match a dirty enum, with a threshold on the count. The whole problem lives in one column: status is written 'Running', 'running' and 'STOPPED' in the same table. Everyone writes GROUP BY region and HAVING COUNT(*) >= 14 in their sleep. What separates people is noticing that a plain status = 'running' quietly drops every row spelled 'Running'. On this data that is not a rounding error. The exact match leaves every region far short of 14, the report comes back empty, and the infra team is told the whole fleet needs provisioning when three regions already meet the policy.

Two filters, two moments

There are two filters here, and they run at two different times. One picks which rows count (running nodes only). The other picks which regions survive (14 or more). Conflating them is where the query goes wrong.

Step 1: Filter rows with a case-folded `WHERE`

WHERE runs before grouping, so this is where you decide what a running node is. LOWER(status) = 'running' is the load-bearing line. Skip the LOWER() and the 'Running' rows silently fall out of your counts, yet the query still runs and returns plausible-looking numbers. That is the dangerous kind of bug: no error, just a wrong answer.

Step 2: Collapse to one row per `region`

GROUP BY region turns the surviving rows into one row per region, and COUNT(*) tallies each bucket. Because WHERE already discarded the stopped, draining and offline nodes, COUNT(*) here means running nodes in this region, which is exactly the metric the policy cares about.

Step 3: Keep only regions over the line with `HAVING`

The 14-or-more test is about the count, which does not exist until after grouping, so HAVING is where you reference COUNT(*). Regions below 14 fall away here, and that is correct: they are the ones needing provisioning, so they do not belong in a report of regions that meet the minimum. On this data the split is real. The regions that come back sit at exactly 14 running nodes, right on the line, and the two that drop land at 12, two short.

Regions with 14+ running nodes

SELECT region, COUNT(*) AS node_count
FROM infra_nodes
WHERE LOWER(status) = 'running'
GROUP BY region
HAVING COUNT(*) >= 14

One scan: case-fold in WHERE, tally with GROUP BY, threshold with HAVING.

Undercounts silently: WHERE status = 'running' matches only the rows spelled exactly lowercase. The 'Running' nodes vanish from the tally, every region drops to 8 or fewer, and the report comes back empty even though three regions already meet the policy.
Correct: WHERE LOWER(status) = 'running' puts every casing variant in the same bucket, so each count reflects reality and the HAVING threshold is applied to a true total.

Common follow-up questions

Along with each qualifying region's `node_count`, also return the count of non-running nodes in that same region. How do you get both without scanning the table twice? (Tests conditional aggregation, `COUNT(*) FILTER (WHERE LOWER(status) = 'running')` beside a total count, versus the instinct to run two queries or self-join.)
The policy changes so a region only qualifies if it has 14+ running nodes AND running nodes are a majority of its total nodes. How does the query change? (Tests combining two group-level predicates in `HAVING` using conditional aggregates over the full region population, not just the filtered rows.)
Instead of a fixed 14, the threshold becomes the top 20% of regions by running-node count. How would you restructure this? (Tests moving from a static `HAVING` constant to a window function (`PERCENT_RANK` / `NTILE`) or a subquery that computes the cutoff dynamically.)