Pod CPU to Memory Ratio

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Airbnb

Problem

Find each namespace's average memory used in k8s_pods, listed from the heaviest footprint down to the lightest. The cluster reliability team is triaging the pods that keep crash-looping instead of starting cleanly, so only those pods count toward the figure.

CPU versus memory. Resource efficiency.

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 tests

This is a scoped group average dressed up as cluster triage: one figure per namespace, taken only over the pods in one status. Anyone can write GROUP BY nspace. What separates candidates is applying the status slice before the average is taken, rather than averaging the whole table and hoping AVG skipping NULLs does the scoping. On this seed that shortcut is not a rounding error. Averaged over every status, 'default' comes out heaviest at about 1894 and 'production' sinks to last near 1771. The crash-looping slice puts 'production' first at 2072. Skip the filter and you send the team to the wrong namespace.

Walk it through

Step 1: Keep only the crash-looping pods

WHERE status = 'CrashLoopBackOff' runs before any grouping, so every pod in another state is gone before a single average is computed. This is the line the whole answer turns on.

Step 2: Collapse to one row per namespace

GROUP BY nspace folds the surviving rows into one row per namespace. Each of the five namespaces has crash-looping pods, so you get five rows.

Step 3: Average the memory with AVG(mem_used)

Every NULL in mem_used belongs to a 'Running' pod, and the filter has already removed those, so no NULL guard is needed. Each namespace averages three or four real readings.

Step 4: List heaviest first

ORDER BY avg_mem_used DESC sorts on the column the query returns. The five averages are distinct, so the heaviest-to-lightest order is fully decided.

The solution

SELECT
    nspace,
    AVG(mem_used) AS avg_mem_used
FROM k8s_pods
WHERE status = 'CrashLoopBackOff'
GROUP BY nspace
ORDER BY avg_mem_used DESC

Every status averaged

Drop the WHERE and each namespace averages between roughly 1771 and 1894, with 'default' on top and 'production' at the bottom.

Crash-looping only

Filter first and the averages spread from 1628 to 2072, with 'production' on top and 'kube-system' at the bottom.

Common follow-up questions

• How would you also report the single heaviest crash-looping pod in each namespace alongside the average? (Extends a per-group aggregate to a per-group pick.)
• If crash-looping pods could have a NULL `mem_used`, what would `AVG` do with them, and would you want that? (Tests whether you know that missing readings change the denominator.)
• The team now wants the same breakdown for 'Error' and 'CrashLoopBackOff' together. How does the filter change? (Tests generalizing a single-value filter to a set of statuses.)