Where the Heat Pools

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

Problem

An on-call engineer is checking where CPU load pools across the fleet, working from the per-node readings in infra_nodes. Show each region with its average CPU utilization, listed alphabetically.

Across the fleet, some regions run hotter than others.

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

k8s_pods

Columns: pod_id INTEGER, pod_name TEXT, nspace TEXT, status TEXT, restarts INTEGER, cpu_used REAL, mem_used REAL

k8s_pods: 10 of 200 rows
pod_id	pod_name	nspace	status	cpu_used	mem_used
2043	payment-003ef	production	Pending	0.011	37
2086	search-003f6	staging	CrashLoopBackOff	0.022	74
2129	gateway-003fd	monitoring	Completed	0.033	111
2172	worker-00404	kube-system	Error	0.044	148
2215	cron-0040b	default	Terminating	0.055	185
2258	ml-serve-00412	production	Running	NULL	NULL
2301	cache-00419	staging	Pending	0.077	259
2344	auth-00420	monitoring	CrashLoopBackOff	0.088	296
2387	payment-00427	kube-system	Completed	0.099	333
2430	search-0042e	default	Error	0.11	370

Expected output

First 5 of 6 rows
region	avg_cpu
ap-south-1	51.72727272727273
eu-central-1	45.8235294117647
eu-west-1	48.94117647058823
sa-east-1	39
us-east-1	49.166666666666664

Worked solution and explanation

What this really is

This is a one-table group average wearing a two-table schema as a costume. The schema drops k8s_pods in front of you, but that table carries no region column and no key that points back at a node, so there is nothing to join it on. The whole answer lives inside infra_nodes. The skill being probed is whether you notice that instead of reflexively pattern-matching two tables into a JOIN, and whether you trust AVG to behave when a node is missing its CPU reading. Reach for a join and you either match nothing or fan the rows out; COALESCE a missing reading to 0 and you quietly understate whichever region that node sits in.

Building it

Step 1: Pick the one table that has the answer

Both region and cpu_pct live on infra_nodes. k8s_pods has no region and no shared key, so it contributes nothing to this answer. Start from infra_nodes alone and ignore the decoy.

Step 2: Collapse to one row per region

Group by region and take AVG(cpu_pct). Every node row folds into the average for its region, leaving one row per region.

Step 3: Order the output

The ask is alphabetical, so order by region. The order is part of what gets checked, and each region appears exactly once, so region alone fixes the sequence with nothing left to tie.

Average CPU per region

SELECT region, AVG(cpu_pct) AS avg_cpu
FROM infra_nodes
GROUP BY region
ORDER BY region;

One scan of infra_nodes; k8s_pods never enters the plan.

Joined the decoy table: Joining k8s_pods produces a broken result: there is no region on that table and no key to match on, so you either get nothing back or a meaningless fan-out, and you lose time hunting a data bug that is not there.
Single table: Reading from infra_nodes alone returns every region with its correct average. Fewer rows scanned, no wasted join, and the query says exactly what it means.

Common follow-up questions

How would the result change if a region had every node's `cpu_pct` null? (Tests whether they know AVG returns null for an all-null group, so the region still appears but with a null average rather than being dropped.)
Now average only across nodes whose status is a running variant, treating 'Running' and 'running' as the same and excluding stopped nodes. (Adds a case-insensitive filter and forces a decision about how to normalize the messy status casing before aggregating.)