Top 10 CPU-Heavy 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: Asana

Problem

Our infrastructure metrics table tracks CPU usage per node. Show the ten nodes consuming the most CPU, with their node ID, hostname, and CPU percentage, sorted from highest utilization to lowest.

The ten hungriest nodes.

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 10 rows
node_id	hostname	cpu_pct
1851	Api-03	99
2702	WEB-03	98
3553	gpu-node-01	97
4404	worker-02	96
1555	api-02	95

Worked solution and explanation

What this really is

This is a top-ten cut over a snapshot dressed up as a capacity question. infra_nodes already holds one row per node, keyed by node_id, so there is nothing to total or average: the whole job is ordering cpu_pct from the top and stopping at ten. Anyone gets the sort. What separates candidates is noticing that the hostname is not the node: 12 hostnames are shared by 200 nodes, and every reading from 99 down to 95 belongs to two different nodes. Group or deduplicate on hostname and you return one row per name, drop half of the real top ten, and report figures no single node ever showed.

Step 1: Confirm the grain is one row per node

node_id is unique across all 200 rows, and cpu_pct is the node's own reading. No GROUP BY, no AVG: project node_id, hostname and cpu_pct straight off the table.

Step 2: Sort the reading from the top

ORDER BY cpu_pct DESC puts the busiest node first. Without it, LIMIT 10 keeps whatever ten rows the scan reads first, and on this table those start at 13, 26 and 39 percent.

Step 3: Cut at ten after the sort

LIMIT 10 applies to the sorted rows, so it keeps the 99 to 95 band: two nodes at each reading. The eleventh row sits at 94, so the cut never splits a tie and no tiebreaker is needed to decide who makes the list.

The solution

Sort `cpu_pct` from the top, keep ten

SELECT node_id, hostname, cpu_pct
FROM infra_nodes
ORDER BY cpu_pct DESC
LIMIT 10

Collapsing by hostname: GROUP BY hostname with MAX(cpu_pct) returns at most 12 rows, one per name. 'Api-03' appears once at 99 even though two different nodes report 99, so the second node at every reading from 99 to 95 vanishes and lower nodes get pulled into the list.
One row per node: Reading the rows as they are keeps both nodes at 99, both at 98 and so on, each with its own node_id. The hostname is a label riding along, never the key.

Common follow-up questions

How would you return the top ten CPU-heavy nodes per `region` instead of overall? (Tests scoping the cut per group: number the rows within each `region` by `cpu_pct` descending and keep positions 1 to 10.)
What if the ten slots were decided by combined CPU and memory pressure? (Tests composite ordering: sort by `cpu_pct + mem_pct`, and say what happens when either reading is missing, since the sum is `NULL` too.)
If only running nodes should compete, how do you filter `status` when it holds both 'running' and 'Running'? (Tests normalizing a dirty category before filtering: compare `LOWER(status)` to 'running' before the sort, so stopped nodes never take a slot.)