Overloaded Infrastructure Nodes

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

Each row of infra_nodes records a node's CPU and memory utilization, and a node is overloaded when its CPU exceeds 90% or its memory exceeds 85%. Surface each unique overloaded node's hostname, region, and node type.

CPU above 90. Memory above 80. Red alert.

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 17 rows
hostname	region	node_type
db-replica	us-east-1	compute
Api-03	ap-south-1	gpu
api-01	us-west-2	memory
Api-03	eu-west-1	storage
gpu-node-01	eu-central-1	general

Worked solution and explanation

This is a two-column OR filter dressed up as capacity alerting, with a dedup hiding under the word 'unique'. Anyone gets cpu_pct > 90 right. What separates candidates is hearing 'or' as a disjunction across two different columns with two different cutoffs, and noticing that infra_nodes records the same hostname, region and node type under more than one node_id. Read 'or' as AND and the alert list is empty on this data: no node runs hot on both at once, so every memory-saturated box with a cool CPU disappears. Skip the dedup and all 17 overloaded nodes show up twice.

Walking to the answer

Step 1: Write the definition as one predicate

cpu_pct > 90 OR mem_pct > 85. Each cutoff belongs to its own column, and both are strict: a node sitting at exactly 90.0 CPU, or exactly 85.0 memory, is not overloaded, and the table has one of each. A node at 95 memory with 65 CPU qualifies on memory alone.

Step 2: Let missing readings fall out

Some rows have no value in either cpu_pct or mem_pct. A comparison against a missing value is never true, so those rows drop out without any extra guard, which matches the business reading: no reading, no alert.

Step 3: Collapse repeated nodes

Every overloaded hostname, region and node type combination sits under two node_id values, so projecting the three columns repeats each node. DISTINCT on the projection returns each one once: 17 rows instead of 34.

OR threshold with DISTINCT

SELECT DISTINCT hostname, region, node_type
FROM infra_nodes
WHERE cpu_pct > 90 OR mem_pct > 85

Both metrics hot: WHERE cpu_pct > 90 AND mem_pct > 85 demands both at once. On this table no node qualifies, so the alert list is empty.
Either metric hot: WHERE cpu_pct > 90 OR mem_pct > 85 flags 17 unique nodes, including 'db-replica' at 95% memory and 65% CPU.

Common follow-up questions

How would you return each overloaded node with which limit it breached: CPU, memory, or both? (Tests deriving a per-row label from the same two conditions.)
If `infra_nodes` kept one reading per minute, how would you flag only nodes that stayed overloaded for five consecutive minutes? (Tests moving from a point filter to a sustained condition over time.)
What changes if a missing `mem_pct` should count as overloaded rather than healthy? (Tests treating missing values as an explicit business decision.)