# The Standing Fleet

> Some hosts wear many hats. Count the machines, not the mentions.

Canonical URL: <https://datadriven.io/problems/the_standing_fleet>

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Google

## 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.

## Tables

### infra_nodes

- node_id INTEGER
- hostname TEXT
- region TEXT
- node_type TEXT
- cpu_pct REAL
- mem_pct REAL
- status TEXT

Sample rows (10 of 200):

| 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 1 of 1 rows:

| 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

**Filter to the out-of-service states, then dedup**

```sql
SELECT COUNT(DISTINCT hostname) AS unique_hosts
FROM infra_nodes
WHERE status IN ('draining', 'offline')
```

> **COUNT(*) counts rows, not machines**
>
> Dropping DISTINCT and writing plain `COUNT(hostname)` tallies matching rows, not machines. Here each affected machine carries sixteen to eighteen inventory rows, so the count jumps from 4 to 68: a shape of the table, not the size of the fleet.

> **Name the states, don't negate one**
>
> A shaky answer reads 'dropped out of active service' as `status <> 'running'`. On this column that also keeps 'Running', 'stopped' and 'STOPPED', and the count jumps to 10. The statement names two states; an explicit `IN ('draining', 'offline')` list is the answer that still holds when a new status value shows up tomorrow.

> **One pass, flat memory**
>
> With a small fleet table and a status filter, this is a single sequential scan. The hash set behind DISTINCT holds at most one entry per matching machine, so memory stays flat no matter how many inventory rows each machine has.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_standing_fleet)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.