Node Summary Per Region

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: Vesta Innovations

Problem

A capacity planner is reviewing the fleet in infra_nodes region by region, where every provisioned node counts toward a region's size whatever its status. Show each region's node count and how many different node_type values appear there, busiest regions first and equally busy ones alphabetically.

Every region has a node story.

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

Worked solution and explanation

This is a rows-versus-values question wearing a capacity-planning badge. Both output columns are counts over the same per-region slice of infra_nodes, and that sameness is the trap. COUNT(*) asks how many nodes, COUNT(node_type) asks how many nodes have a type, and only COUNT(DISTINCT node_type) asks how many different types there are. Regions here hold up to 40 nodes of a single type, so the wrong second count reports 40 types where there is one.

Walk it

Step 1: Slice the fleet by `region`

One output row per region means GROUP BY region. There is no status filter: a stopped or draining node still occupies capacity, so 'Running', 'STOPPED' and the rest all count. Slip in WHERE status = 'running' and the region totals shrink, which reorders a list meant to show provisioned size.

Step 2: Count rows, then count values

COUNT(*) gives total_nodes. For unique_types the question is about values, so repeats must collapse: COUNT(DISTINCT node_type). In 'eu-west-1' it comes back 2, because one node is recorded as 'Storage' and the rest as 'storage'; the count reports values as recorded.

Step 3: Sort by the count you just named

ORDER BY total_nodes DESC puts the 40-node regions on top. Four regions tie there, so region settles them alphabetically ('eu-central-1', 'eu-west-1', 'us-east-1', 'us-west-2'), then 'ap-south-1' at 39 and 'sa-east-1' at 1 follow.

Node count and type count per region

SELECT
    region,
    COUNT(*) AS total_nodes,
    COUNT(DISTINCT node_type) AS unique_types
FROM infra_nodes
GROUP BY region
ORDER BY total_nodes DESC, region

Counting the column: COUNT(node_type) skips only NULLs, and node_type has none, so it returns 40, 40, 40, 40, 39, 1: the same numbers as total_nodes, a second column that says nothing new.
Counting its values: COUNT(DISTINCT node_type) collapses repeats before counting, returning 1 for every region except 'eu-west-1', which shows 2.

Common follow-up questions

How would `unique_types` change if 'Storage' and 'storage' must count as one type? (Normalizing case inside the distinct count.)
Add each region's share of the whole fleet as a percentage. (A fleet-wide total alongside the per-region count.)
List only the regions that run more than one node type, with the types they run. (Filtering groups on an aggregate and collecting values.)