# The Stable and the Restless > Some pods never restart. That could mean anything. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## 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** ```sql SELECT status, COUNT(*) AS pod_count FROM k8s_pods WHERE restarts = 0 GROUP BY status ORDER BY pod_count DESC, status ``` > **WHERE, not HAVING** > > The restart condition looks at an individual pod's raw value, so it belongs in WHERE and runs before grouping. HAVING filters whole groups after aggregation; put restarts = 0 there and you either error out on the non-aggregated column or accidentally distort which statuses survive. Rule of thumb: a condition on a raw column is a WHERE, a condition on an aggregate is a HAVING. > **Interviewers watch for** > > They watch whether you filter before you group without being told, whether you count with `*` rather than a nullable column, and whether you give a deterministic order. Naming the tie-break unprompted reads as production instinct rather than homework. > **Common pitfall** > > The classic miss is grouping by `restarts` instead of `status`. The prompt says restarts, so a hurried candidate buckets by it, but after the WHERE every surviving row has restarts = 0, so that grouping yields a single meaningless row. Ask what actually varies in the output, and bucket by that. > **Cost analysis** > > The scan touches 1,500,000 rows, but `status` has only five distinct values, so the grouping hash is tiny and the aggregation is nearly free. The real cost is the WHERE restarts = 0 filter. If this runs often, a partial index on restarts covering status lets the engine read just the never-restarted subset instead of the full table. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_stable_and_the_restless) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.