# The Vital Signs > Each check records a verdict. See how they stack up. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem We run periodic health checks against auth-svc, and ops wants to see the spread of outcomes. Report how many of its checks landed at each status value. ## Worked solution and explanation ### What this is really asking Strip the ops framing and this is a per-status tally of a single service's health checks: filter svc_health to auth-svc, bucket by status, count each bucket. The skill being probed is whether you trust the data exactly as stored. status arrives unnormalized, so 'Healthy' and 'healthy' are different byte strings, and SQLite groups them as different buckets by default. Reach for LOWER(status) to 'tidy' that up and you fuse buckets ops tracks separately, dropping five rows to three, and your counts stop reconciling with the raw auth-svc row total. One pass with one aggregate is all this needs; anything fancier is a way to get it wrong. --- ### Break down the requirements #### Step 1: Filter to one service WHERE svc_name = 'auth-svc'. Plain equality, not LIKE: the ask names the service exactly, and auth-svc is a rare value in a 25M-row table, so this predicate does almost all the row elimination. #### Step 2: Bucket by status as stored GROUP BY status. SQLite compares TEXT byte for byte under the default collation, so 'Healthy' and 'healthy' land in separate buckets. That is exactly what the expected preview shows, so group the values as stored and do not wrap status in LOWER(). #### Step 3: Count each bucket COUNT(*) AS check_count. status has no nulls here, so COUNT(*) and COUNT(status) agree, but COUNT(*) is the honest default and keeps each bucket's count summing back to the auth-svc row total. --- ### The solution **AUTH-SVC STATUS TALLY** ```sql SELECT status, COUNT(*) AS check_count FROM svc_health WHERE svc_name = 'auth-svc' GROUP BY status ``` > **Cost Analysis** > > The table is partitioned by checked, which this query never predicates on, so every partition is read: a full scan is unavoidable here. Status cardinality is tiny, so the hash aggregate is nearly free and the scan dominates the cost. A composite index on (svc_name, status) would let the planner seek to the auth-svc rows and count from the index, skipping the heap entirely. > **Trick to solving** > > Read the expected preview before you write any SQL. Seeing DEGRADED and degraded sitting as two separate rows tells you the answer keeps case intact, which means the whole problem is a plain per-status tally with zero normalization. The data hands you the spec. > **Common Pitfall** > > Reaching for LOWER(status) or UPPER(status) to 'clean up' the mixed case. That collapses 'Healthy' and 'healthy' into one bucket, cuts the result from five rows to three, and the numbers no longer reconcile with what ops actually logged. Group the values as they are stored. > **Interviewers Watch For** > > Whether you NOTICE the mixed case in the preview and ask about it rather than silently normalizing. status is almost certainly meant to be an enum, so the case drift is a data-quality smell. Calling it out, then grouping as stored because that is what the result wants, is the tell that separates a careful engineer from a reflexive one. **GROUP BY status** Five buckets: DEGRADED, Healthy, degraded, healthy, timeout. Mirrors exactly what ops recorded, and the bucket counts sum back to auth-svc's row total. This is the answer. **GROUP BY LOWER(status)** Three buckets: degraded, healthy, timeout. Tidier, but it fuses cases that were logged separately and answers a question nobody asked. Only correct if the interviewer confirms the case drift is noise. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - Break this down by region as well. _(Adds region to both the projection and the grouping, and probes whether you flatten to one grain or pivot to a wide layout.)_ - Restrict to the last 24 hours. _(Adds AND checked >= datetime('now', '-1 day'), which finally activates partition pruning on the checked key.)_ - Show each status as a percentage of total auth-svc checks. _(Forces COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () or a scalar subquery in the denominator to express each bucket as a share of total auth-svc checks.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_vital_signs) - [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.