Average GPU Node CPU Usage
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
Chasing throttled ML training jobs, an on-call engineer wants to check whether the GPU nodes in infra_nodes are running hot, and a node that never reported a CPU reading tells her nothing either way. What is the average CPU percentage across the nodes typed 'gpu'?
GPU nodes burning CPU. How much?
Tables
infra_nodes
Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT
| 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
| avg_cpu_pct |
|---|
| 51.35294117647059 |
Worked solution and explanation
Why this problem exists in real interviews
This is a subgroup average dressed up as a capacity investigation. Anyone can type AVG(cpu_pct). What separates candidates is what they say before typing: the average has to be scoped to the gpu nodes, and the six gpu nodes that never reported a reading have to fall out rather than count as zero. Average the whole of infra_nodes and you report the fleet. Zero-fill the gaps and you report 43.65 instead of 51.35, and the team misreads how hot the GPU tier is.
Break down the requirements
Step 1: Pin the grain
Ask: is infra_nodes one row per node, or one snapshot per node over time? If snapshots, your average mixes nodes and time. Here node_id is unique across the 200 rows, so it is one row per node; say that you checked.
Step 2: Filter on node_type
WHERE node_type = 'gpu' is the cohort. Look at the values before trusting the literal: node_type does carry a 'Storage' case variant, but every gpu node is spelled 'gpu', so the plain equality catches all 40.
Step 3: AVG handles NULL on its own
AVG(cpu_pct) skips NULL rows in both numerator and denominator, so 34 readings are averaged, not 40. No COALESCE, no WHERE cpu_pct IS NOT NULL. Flag the assumption out loud: missing telemetry is excluded, not zeroed.
Step 4: Alias the scalar
Return AVG(cpu_pct) AS avg_cpu_pct. Single scalar, one row out. No GROUP BY needed when every selected expression is an aggregate.
The solution
SELECT AVG(cpu_pct) AS avg_cpu_pct
FROM infra_nodes
WHERE node_type = 'gpu';COMMON FOLLOW-UP QUESTIONS
Common follow-up questions
- How would the query change if `infra_nodes` stored hourly snapshots instead of current state? (Probes grain awareness and whether you per-node average first to avoid weighting noisy nodes.)
- Return average CPU for every `node_type`, sorted highest first. (Forces the jump from scalar aggregate to `GROUP BY node_type`, and whether you notice 'storage' and 'Storage' split into two groups.)
- Only count GPU nodes that are currently running. What changes? (Tests whether you add the predicate to `WHERE` and normalize `status`, since it holds both 'running' and 'Running'.)
- How would you flag GPU nodes whose CPU is more than two standard deviations above the GPU mean? (Opens window functions: `AVG` and `STDDEV` `OVER (PARTITION BY node_type)`.)