# The Loudest Neighbor

> In every namespace, one pod does most of the eating.

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Deloitte

## Problem

List each namespace's featherweight from `k8s_pods`, the one pod using the least memory, alongside that memory, so we can rightsize our multi-tenant Kubernetes cluster. Pods still starting up haven't reported memory and can't qualify, and when two pods tie for the lightest we keep the one whose name comes first alphabetically.

## Tables

### k8s_pods

- pod_id INTEGER
- pod_name TEXT
- nspace TEXT
- status TEXT
- restarts INTEGER
- cpu_used REAL
- mem_used REAL

Sample rows (10 of 200):

| pod_id | pod_name | nspace | status | restarts | cpu_used | mem_used |
| --- | --- | --- | --- | --- | --- | --- |
| 2043 | payment-003ef | production | Pending | 0 | 0.011 | 37 |
| 2086 | search-003f6 | staging | CrashLoopBackOff | 0 | 0.022 | 74 |
| 2129 | gateway-003fd | monitoring | Completed | 0 | 0.033 | 111 |
| 2172 | worker-00404 | kube-system | Error | 0 | 0.044 | 148 |
| 2215 | cron-0040b | default | Terminating | 0 | 0.055 | 185 |
| 2258 | ml-serve-00412 | production | Running | 0 | NULL | NULL |
| 2301 | cache-00419 | staging | Pending | 0 | 0.077 | 259 |
| 2344 | auth-00420 | monitoring | CrashLoopBackOff | 0 | 0.088 | 296 |
| 2387 | payment-00427 | kube-system | Completed | 0 | 0.099 | 333 |
| 2430 | search-0042e | default | Error | 0 | 0.11 | 370 |

## Expected output

First 5 of 5 rows:

| nspace | pod_name | mem_used |
| --- | --- | --- |
| default | cron-0040b | 185 |
| kube-system | worker-00404 | 148 |
| monitoring | gateway-003fd | 111 |
| production | payment-003ef | 37 |
| staging | search-003f6 | 74 |

## Worked solution and explanation

### What this problem really is

Under the Kubernetes costume this is **argmin per group**: for each `nspace`, return the row that holds the minimum memory, not just the minimum value. Anyone can write `MIN(mem_used)` with a `GROUP BY nspace`. What separates candidates is what goes next to it. Once you also select `pod_name`, a plain aggregate no longer promises that the name belongs to the pod holding the min. There is a second trap: a pod still starting up reports a `NULL` `mem_used`. On engines that sort nulls first, it lands at the front and passes for the lightest pod. Get either wrong and you flag a pod for downsizing that is actually running hot.

### The trap

**Naive: MIN in a grouped query**

`SELECT nspace, pod_name, MIN(mem_used) ... GROUP BY nspace`. In portable SQL the bare `pod_name` is undefined, so the engine may pick any row in the group. You return a correct number attached to the wrong pod.

**Correct: filter, number, keep one**

Drop `NULL` readings with `WHERE mem_used IS NOT NULL`, number pods inside each `nspace` with `ROW_NUMBER()`, and keep `mem_rank = 1`. The surviving row carries `nspace`, `pod_name` and `mem_used` together, so the name and the value come from the same pod.

> **SQLite hides the bare-column bug**
>
> SQLite returns the bare columns from the row that produced a lone `MIN()` or `MAX()`, so the naive query looks right in a SQLite console. It breaks on Postgres or Spark, or as soon as a second aggregate is added. Make the row-carrying logic explicit instead of relying on that quirk.

### Building it

#### Step 1: Name the pattern

Say it out loud first: bottom-1-per-group on `mem_used`, partitioned by `nspace`. That tells the interviewer you already know a bare `GROUP BY` cannot carry the winner's identity.

#### Step 2: Filter `NULL` before ranking

A `NULL` `mem_used` is not the smallest reading. It means the pod has not reported yet. The filter has to run in the inner `WHERE`, before the window. If you filter afterward, the null pod has already taken rank one and the real lightest pod is gone.

#### Step 3: Rank inside each namespace

`ROW_NUMBER() OVER (PARTITION BY nspace ORDER BY mem_used ASC, pod_name)` gives exactly one rank-one row per partition. The `pod_name` tie-break makes the winner deterministic when two pods share the lowest reading.

#### Step 4: Keep `mem_rank = 1`

The outer query keeps the winners. The result is graded as a set, so a trailing `ORDER BY mem_used` only changes presentation. Add it if a human will read the list, and leave the logic as it is.

### The solution

**Lightest-memory pod per namespace**

```sql
SELECT nspace,
       pod_name,
       mem_used
FROM (
  SELECT nspace,
         pod_name,
         mem_used,
         ROW_NUMBER() OVER (
           PARTITION BY nspace
           ORDER BY mem_used ASC, pod_name
         ) AS mem_rank
  FROM k8s_pods
  WHERE mem_used IS NOT NULL
) ranked
WHERE mem_rank = 1
```

> **One sort, no self-join**
>
> Over 3,000,000 rows the engine does a single sort on (`nspace`, `mem_used`) and keeps the first row of each partition, roughly O(n log n). A composite index on (`nspace`, `mem_used`) lets the planner read rows in order and skip the sort.

> **State the tie rule before you type**
>
> Two pods in one namespace can report identical memory. Strong candidates say up front whether they return one winner (broken on `pod_name`) or every co-leader. Weak ones ship a query whose output changes between runs.

The correlated-subquery version below is the one people often reach for. It is shown as a contrast, not an answer. It re-scans `k8s_pods` once per outer row. Its outer query never filters `NULL`, which happens to be harmless here, but only by accident. Most importantly, it returns every pod tied for the minimum, so a namespace with a tie produces two rows and fails the one-per-namespace contract.

**Contrast: correlated subquery returns ties**

```sql
SELECT p.nspace,
       p.pod_name,
       p.mem_used
FROM k8s_pods p
WHERE p.mem_used = (
  SELECT MIN(p2.mem_used)
  FROM k8s_pods p2
  WHERE p2.nspace = p.nspace
    AND p2.mem_used IS NOT NULL
)
```

*Ties on the minimum produce several rows per `nspace`, which breaks the one-winner rule.*

### Follow-ups

## Common follow-up questions

- Return the three lightest pods per namespace instead of one. _(Generalizes `mem_rank = 1` to `mem_rank <= 3` while staying a single pass.)_
- How would you return every pod tied for the minimum? _(Tests whether they switch `ROW_NUMBER()` to `RANK()` on purpose.)_
- What index makes this fast on 100M rows, and how does the plan change? _(Index reasoning: (`nspace`, `mem_used`) removes the sort.)_
- Also report the winner's share of its namespace's total `mem_used`. _(Combines a windowed `SUM()` with the rank in the same pass.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_loudest_neighbor)
- [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.