Multi-Host Regions by Node 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: Google

Problem

A region with only one or two compute, storage, network or GPU nodes is too thin for the capacity analyst's density review, and a hostname that sits on several of those nodes still counts as one host. Using infra_nodes, list each region with more than two such nodes and the number of unique hostnames behind them.

Some regions are quietly building empires.

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	unique_hostnames
ap-south-1	12
eu-west-1	12
us-east-1	12

Worked solution and explanation

The trap

This is a rows-versus-identities problem wearing a capacity-planning badge. infra_nodes holds one row per provisioned node, and the same hostname is reused across many of them: a region with forty nodes can be running on eight names. Everyone filters the node types and groups by region. What separates candidates is keeping two counts straight: the thin-region bar is about nodes, the reported figure is about hosts. Let the reported figure count rows and every busy region shows its node count as its host count, inflated several times over.

Build it

Step 1: Keep only the four node types

Filter node_type with IN ('compute', 'storage', 'network', 'gpu') in the WHERE. 'memory' and 'general' nodes are out of scope, and dropping them before grouping keeps them out of both counts.

Step 2: Group by `region` alone

The answer is one row per region, so region is the only key. Adding node_type would split a region across rows and count a hostname serving two types twice.

Step 3: Count hosts, not rows

COUNT(DISTINCT hostname) collapses every reuse of a name into one host. COUNT(hostname) looks identical at a glance and quietly returns the node count under a host label.

Step 4: Gate on node volume after grouping

The thin-region rule is about nodes, so HAVING COUNT(*) > 2 tests rows per region once they are grouped. It cannot sit in WHERE, which only ever sees one node at a time.

Two counts, two questions

SELECT
    region,
    COUNT(DISTINCT hostname) AS unique_hostnames
FROM infra_nodes
WHERE node_type IN ('compute', 'storage', 'network', 'gpu')
GROUP BY region
HAVING COUNT(*) > 2

The gate counts nodes; the reported column counts the names those nodes run under.

Counting rows: COUNT(hostname) adds one per node. A region whose nodes reuse eight names across forty rows reports 40 hosts.
Counting hosts: COUNT(DISTINCT hostname) adds one per name. The same region reports 8, the number of hosts capacity planning actually has to size.

Common follow-up questions

How would you also report the node count per region next to `unique_hostnames`? (Checks that the candidate can surface both counts from one pass without a second scan.)
What changes if some rows store 'Storage' instead of 'storage' in `node_type`? (Tests whether they notice inconsistent casing in a filter column and normalize it deliberately.)
How would you find hostnames that appear in more than one region? (Flips the grouping key and tests the same rows-versus-identities reasoning from the other side.)