Top 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

L5

Asked in

City and County of San Francisco

Problem

Each row of k8s_pods is a CPU sample of a pod in its namespace, and the same pod can be sampled on more than one row; a pod whose status is 'Completed' has finished and does not count. Produce one row per namespace naming its highest-CPU pod and its runner-up in separate columns, with the namespaces in alphabetical order.

The two most CPU-hungry pods in each 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

What this really is

This is a per-namespace top two dressed up as a capacity report, with two grain problems hiding underneath. Anyone can write a ranking. What separates candidates is noticing that k8s_pods holds every pod twice (two pod_id values, same pod_name, same cpu_used) and that finished pods are still sitting in the table. Rank the raw rows and the leader fills both columns. Skip the 'Completed' filter and kube-system's leader is a pod that already exited.

Step 1: Drop finished pods first

WHERE status <> 'Completed' runs on the raw rows, before the collapse. On this data it decides two answers: in 'kube-system' the highest raw reading belongs to a 'Completed' pod (gateway-0069d), and in 'monitoring' so does the runner-up (cron-00673). Without it you are ranking pods that no longer use any CPU.

Step 2: Collapse each pod_name to one reading

Group by nspace, pod_name and keep MAX(cpu_used). The two rows per pod carry identical readings today. MAX is still the honest choice, because it stays right on the day a pod's samples disagree.

Step 3: Number pods inside each namespace

ROW_NUMBER() OVER (PARTITION BY nspace ORDER BY cpu_used DESC, pod_name ASC) restarts the count per namespace. 'Running' pods carry a NULL cpu_used in this data, and under DESC those sort after every real reading, so they never take a slot. pod_name settles any CPU tie the same way on every run.

Step 4: Spread positions one and two into columns

MAX(CASE WHEN rn = 1 THEN pod_name END) keeps only the first-place name per namespace, and the rn = 2 twin keeps the runner-up. An rn <= 2 filter adds nothing here: pods ranked third or lower produce NULL in both expressions, and MAX skips NULLs.

WITH pod_cpu AS (
    SELECT nspace, pod_name, MAX(cpu_used) AS cpu_used
    FROM k8s_pods
    WHERE status <> 'Completed'
    GROUP BY nspace, pod_name
),
ranked AS (
    SELECT nspace, pod_name,
           ROW_NUMBER() OVER (
               PARTITION BY nspace
               ORDER BY cpu_used DESC, pod_name ASC
           ) AS rn
    FROM pod_cpu
)
SELECT nspace,
       MAX(CASE WHEN rn = 1 THEN pod_name END) AS highest_cpu_pod,
       MAX(CASE WHEN rn = 2 THEN pod_name END) AS second_highest_cpu_pod
FROM ranked
GROUP BY nspace
ORDER BY nspace

Ranking raw sample rows

Every pod has two rows with the same cpu_used, so the leader takes both rn = 1 and rn = 2. The 'default' row comes back as worker-006a4 in both columns.

Ranking collapsed pods

After GROUP BY nspace, pod_name each pod is one row, so rn = 2 is a different pod, and 'default' reads worker-006a4 then cache-00681.

Common follow-up questions

• If a namespace has only one live pod, what appears in `second_highest_cpu_pod`? (Tests the pivot's NULL behavior: no row has `rn = 2`, so the `MAX` returns NULL.)
• How would you return the top three, or make N a parameter? (Tests whether the candidate sees that a fixed column set needs one `CASE` per position, and when an array or a long format is the better output.)
• What if `status` values arrive in mixed case, like 'completed'? (Tests normalizing the filter column before comparing it.)