The Standing Fleet
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
Problem
The on-call team is auditing our node fleet for machines that have dropped out of active service. Among the nodes marked draining or offline, count how many different machines are affected, counting a machine that appears under several node types only once.
Some hosts wear many hats. Count the machines, not the mentions.
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
| unique_hosts |
|---|
| 4 |
Worked solution and explanation
What this really is
Beneath the audit story this is a filter-then-dedup. Narrow the fleet to the two out-of-service states, then count machines, not the rows that mention them. The catch is that this inventory lists every machine on many rows, once per node type and region, so COUNT(*) over the filtered rows counts entries and reports 68 where only 4 machines are actually affected. The other miss is paraphrasing the scope: status also holds 'Running', 'stopped' and 'STOPPED', so a filter written as status <> 'running' sweeps in parked and mis-cased rows and reports 10 machines.
Working through it
Step 1: Scope to the out-of-service states
WHERE status IN ('draining', 'offline') keeps only the machines that have left active service. Match the literals to the values stored in the column: these two states are each written in a single casing, so a mistyped 'Offline' or a status that isn't there would quietly match nothing.
Step 2: Count each machine once
COUNT(DISTINCT hostname) collapses the many inventory rows for one machine down to a single tally. This is the line that makes the answer 'how many machines' rather than 'how many rows', since every host appears once per node type and region.
The solution
SELECT COUNT(DISTINCT hostname) AS unique_hosts
FROM infra_nodes
WHERE status IN ('draining', 'offline')Common follow-up questions
- How would you report the affected-machine count broken down by node type? (Add GROUP BY `node_type` so each tier reports its own affected-machine count.)
- What if you wanted every machine that is not currently running? (Widen the IN-list to include the stopped states, remembering the casing variants like STOPPED and stopped.)
- How would you list which machines are affected instead of just the count? (Swap the aggregate for SELECT DISTINCT hostname to surface the machines themselves.)