The Loudest Neighbor

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

k8s_pods

Columns: pod_id INTEGER, pod_name TEXT, nspace TEXT, status TEXT, restarts INTEGER, cpu_used REAL, mem_used REAL

Expected output

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.

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

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

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.

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
)

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