Average Node Utilization

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

Problem

The quarterly capacity review is tomorrow and the VP wants a utilization heatmap by region and node type. Show average CPU and memory utilization for each combination so the team can spot over- and under-provisioned segments.

CPU and memory, region by region.

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 7 rows
region	node_type	avg_cpu_pct	avg_mem_pct
ap-south-1	gpu	51.72727272727273	46.696969696969695
eu-central-1	general	45.8235294117647	45.705882352941174
eu-west-1	Storage	NULL	NULL
eu-west-1	storage	48.94117647058823	48
sa-east-1	gpu	39	57

Worked solution and explanation

What this is really asking

infra_nodes carries cpu_pct and mem_pct per host, and the VP wants one cell per (region, node_type). The whole problem is grouping by both keys at once, not separately.

Break down the requirements

Step 1: Group by the composite key

GROUP BY region, node_type creates one row per cell of the heatmap. Grouping by either column alone collapses the other dimension and the VP loses the segment view.

Step 2: Two averages, one pass

AVG(cpu_pct) AS avg_cpu_pct and AVG(mem_pct) AS avg_mem_pct in the same SELECT. Both aggregates share the same group, so this is a single scan over 10k rows.

The solution

AVG UTILIZATION BY REGION AND TYPE

SELECT
    region,
    node_type,
    AVG(cpu_pct) AS avg_cpu_pct,
    AVG(mem_pct) AS avg_mem_pct
FROM infra_nodes
GROUP BY region, node_type

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

How would you flag cells that are over-provisioned? (Wrap the query in a CTE and filter where `avg_cpu_pct` < 20 AND `avg_mem_pct` < 20, or threshold against a target utilization band.)
What if some regions have only one node of a type? (AVG over one row equals that row. Add COUNT(*) to the projection so the VP can see sample size before trusting outliers.)
How would you compare this quarter to last quarter? (You need a snapshot column like `captured_at`. Self-join the aggregated CTE on (region, `node_type`) across two periods and diff the averages.)