Heavy Namespaces

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: L3

Problem

A pod in k8s_pods only counts as failing when its status is 'Error' or 'CrashLoopBackOff', and the SRE team's cluster triage flags any namespace holding more than 12 failing pods. List the flagged namespaces with how many failing pods each one holds.

Kubernetes has favorites. Some namespaces carry more weight.

Tables

k8s_pods

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

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

Expected output

First 4 of 4 rows
nspace	failing_pods
default	14
kube-system	14
monitoring	14
staging	14

Worked solution and explanation

What this is really asking

This is a two-stage filter wearing a Kubernetes costume. A row-level filter, WHERE status IN ('Error', 'CrashLoopBackOff'), decides which pods count as failing; then, and only then, a group-level filter, HAVING COUNT(*) > 12, decides which namespaces made enough noise to report. The trap is collapsing the two stages: put the count in WHERE and the engine rejects it, drop the status filter and you count healthy pods too. Get either wrong and production, sitting at exactly 12 failing pods, slips into a list it does not belong on.

Break down the requirements

Step 1: Filter to failing pods first

WHERE status IN ('Error', 'CrashLoopBackOff') runs per row, before any grouping, so only failing pods survive to be counted. Skip it and COUNT(*) tallies every pod in the namespace, healthy ones included, and the whole answer shifts.

Step 2: Group and count

GROUP BY nspace collapses the survivors to one row per namespace, and COUNT(*) is now exactly the number of failing pods in each.

Step 3: Filter the groups

HAVING COUNT(*) > 12 keeps only the loud namespaces. WHERE cannot do this: the count does not exist until after GROUP BY. Alias it failing_pods so the result reads cleanly.

The solution

FAILING PODS BY NAMESPACE

SELECT nspace,
       COUNT(*) AS failing_pods
FROM k8s_pods
WHERE status IN ('Error', 'CrashLoopBackOff')
GROUP BY nspace
HAVING COUNT(*) > 12

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

How would you also report how many pods in each namespace are still `Running`? (Add a `SUM(CASE WHEN status = 'Running' THEN 1 ELSE 0 END)` alongside the failing count so one pass reports both.)
How would you rank namespaces by their share of all failing pods? (Wrap the aggregate in a CTE and divide each `failing_pods` by `SUM(failing_pods) OVER ()` to get each namespace's share of cluster failures.)
What changes if `nspace` can be `NULL`? (`GROUP BY` folds every `NULL` namespace into one bucket; decide whether that is a real namespace or a data-quality issue before reporting it.)