CPU Utilization Summary

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

Problem

The capacity planning team wants a one-line summary of fleet-wide CPU health: the minimum, average, and maximum CPU utilization across all infrastructure nodes.

The CPUs are working. How hard?

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 1 of 1 rows
min_cpu	avg_cpu	max_cpu
0	48.77906976744186	99

Worked solution and explanation

Why this problem exists in real interviews

This tests basic aggregate functions (MIN, AVG, MAX) without GROUP BY. It produces a single summary row, screening for fundamental aggregate fluency.

Break down the requirements

Step 1: Compute three aggregates

MIN(cpu_pct), AVG(cpu_pct), and MAX(cpu_pct) in a single SELECT without GROUP BY produces one summary row.

The solution

Fleet-wide aggregate summary

SELECT
    MIN(cpu_pct) AS min_cpu,
    AVG(cpu_pct) AS avg_cpu,
    MAX(cpu_pct) AS max_cpu
FROM infra_nodes

Common follow-up questions

How would you break this down by region? (Add GROUP BY region.)
What if you also needed the standard deviation? (Add STDDEV(`cpu_pct`) or `STDDEV_POP`(`cpu_pct`).)
How would you identify nodes in the top 5% of CPU utilization? (Tests `PERCENTILE_CONT` or NTILE for percentile-based filtering.)