Storage Node Lookup
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
- City and County of San Francisco
Problem
Find all nodes with a storage-type designation, along with each node's hostname and CPU percentage.
The storage nodes hold the critical data.
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
| hostname | cpu_pct |
|---|---|
| api-01 | 26 |
| worker-01 | NULL |
| web-01 | 56 |
| db-replica | 21 |
| WEB-03 | 86 |
Worked solution and explanation
What this really is
This is a case-folding problem dressed up as a one-line filter. Projecting hostname and cpu_pct from infra_nodes is the easy part. The skill being probed is whether you look at node_type before trusting it. The column was never normalized on write: worker-01 is stored as 'Storage' while every other storage node says 'storage'. Write node_type = 'storage' and you get 39 rows instead of 40. The result looks complete and silently drops a storage node from the inventory.
Step 1: Read the categorical before you filter on it
The sample rows already show 'Storage' sitting next to 'storage'. Whenever a label that drives a filter comes from people or from several writers, assume its casing drifts until the data proves otherwise.
Step 2: Fold node_type, not the literal
LOWER(node_type) = 'storage' maps every spelling to one value, so 'Storage', 'STORAGE' and 'storage' all match. Changing the case of the literal fixes nothing, because the mismatch lives in the column.
Step 3: Project hostname and cpu_pct, and keep the gaps
The filter only touches node_type, so the six storage nodes with no CPU reading, worker-01 among them, come back with a blank cpu_pct. Do not add a cpu_pct IS NOT NULL guard nobody asked for: those nodes are still storage nodes.
SELECT hostname, cpu_pct
FROM infra_nodes
WHERE LOWER(node_type) = 'storage'- Exact match
WHERE node_type = 'storage'returns 39 rows.worker-01, stored as 'Storage', disappears and nothing errors.- Folded match
WHERE LOWER(node_type) = 'storage'returns all 40, includingworker-01with its missingcpu_pct.
Common follow-up questions
- What if some `node_type` values also carry stray spaces, like ' storage'? (Tests stacking `TRIM` with `LOWER` on the column side of the comparison.)
- How would you count storage nodes per `region` while keeping the casing fix? (Tests carrying the folded value into the grouping so 'Storage' and 'storage' do not split into two groups.)
- Which index helps this query once `infra_nodes` holds hundreds of millions of rows? (Tests knowing that an index on `node_type` cannot serve `LOWER(node_type)`, while an expression index or a normalized column can.)