The Stable and the Restless

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: City and County of San Francisco

Problem

The reliability team is auditing pods that have never restarted. Break those down by status, showing how many fall into each, most common first.

Some pods never restart. That could mean anything.

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 5 of 6 rows
status	pod_count
CrashLoopBackOff	32
Pending	32
Completed	30
Error	30
Terminating	28

Worked solution and explanation

What this really is

This is a filtered frequency count wearing a Kubernetes costume. The real question: of the pods that have never restarted, how many sit in each lifecycle status? The skill being probed is scoping the population BEFORE you tally it. Restart count is a row filter (WHERE restarts = 0); status is the thing you bucket by. Candidates who blur those two group by restarts and hand back a histogram nobody asked for, or push the restart condition into HAVING and quietly drop the very buckets they were supposed to count.

Building it

Step 1: Scope to the survivors

WHERE restarts = 0 keeps only pods that have never restarted. It runs before any grouping, so it shrinks the population you are about to bucket. This is a per-row condition on a raw column, which is exactly what WHERE is for.

Step 2: Bucket by status

GROUP BY status collapses the survivors into one row per lifecycle status. With only five distinct statuses, the result is a compact, readable breakdown.

Step 3: Tally each bucket

COUNT(*) tallies the pods in each status group. Use * because you are counting pods, not the non-null values of some particular column.

Step 4: Most common first

ORDER BY pod_count DESC puts the busiest status on top. A secondary sort on status keeps equal counts in a stable, predictable order so the same query returns the same order every run.

The solution

Filtered status breakdown of never-restarted pods

SELECT status, COUNT(*) AS pod_count
FROM k8s_pods
WHERE restarts = 0
GROUP BY status
ORDER BY pod_count DESC, status

Common follow-up questions

A status with zero never-restarted pods will not appear in your result. If the team needs every status listed, including the empty ones, how would you produce that? (Tests whether the candidate reaches for a left join against a status dimension to surface empty buckets.)
Two statuses tie on `pod_count`. What determines their order, and how do you make the result reproducible from one run to the next? (Tests tie-breaking and deterministic ordering.)
restarts is stored as an exact integer with no nulls today. If it became nullable, would restarts = 0 still capture what the team means by 'never restarted'? (Tests null semantics in filter predicates.)