Lowest CPU Pods per Namespace

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

City and County of San Francisco

Problem

For each Kubernetes namespace, surface the five pods with the lowest CPU usage, treating a pod with no recorded CPU as the lowest. Pods sharing a CPU value take the same position with no gap after them, so a namespace with a tie at the cutoff can return more than five rows. Show each pod's name, namespace, CPU, and memory usage, grouped by namespace with the lowest usage first and ties broken by pod id.

The five lightest pods per namespace.

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

Why this problem exists in real interviews

This is a per-group top-N hiding inside a capacity-planning ask: keep the five lightest pods in every one of ~86k namespaces, in a single pass over 3M rows. Anyone can sort and cap; the separator is picking the window that keeps tied pods together at the boundary and then making the final order total so a correct answer never grades as wrong on a tie.

Break down the requirements

Step 1: Rank within each namespace

Partition by nspace and order by cpu_used ascending. Nulls sort first in ascending order, which is exactly what we want: a pod with no recorded CPU is treated as the lowest and stays in contention for the five.

Step 2: Rank with DENSE_RANK for tie inclusion

DENSE_RANK() assigns the same rank to tied CPU values and never skips a number, so filtering to rnk <= 5 keeps a full band of five usage levels. When the fifth level is tied, every pod at that level survives, so a namespace can legitimately return more than five rows.

Step 3: Make the final order total

Order the output by nspace, then rnk, then pod_id. The first two group each namespace's pods lowest-CPU first, but they are not unique when pods tie, so pod_id (unique per pod) makes the sort TOTAL: no two rows share every sort key, and a correct answer can never lose to arbitrary engine order.

The solution

SELECT pod_name, nspace, cpu_used, mem_used
FROM (
    SELECT pod_name, nspace, cpu_used, mem_used, pod_id,
        DENSE_RANK() OVER (PARTITION BY nspace ORDER BY cpu_used ASC) AS rnk
    FROM k8s_pods
) ranked
WHERE rnk <= 5

Common follow-up questions

• Nulls in `cpu_used` sort first here. How would you change the query if a missing reading should instead be treated as the HIGHEST usage? (Tests whether the candidate reasons about null ordering rather than assuming nulls are excluded.)
• If two pods in a namespace have identical `cpu_used`, how does each of the three ranking functions treat them at the five-row cutoff? (Tests understanding of RANK vs `DENSE_RANK` vs `ROW_NUMBER` tie-breaking behavior.)
• What index on `k8s_pods` would let the engine avoid a full sort for the per-namespace ranking? (Tests indexing knowledge specific to the partition and sort keys of the window.)