# Under the Same Name > The same state wears different casing. Read the latency behind each one. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Service agents record a status for every health check, but different agents write the same state in different letter casing, so 'Healthy' and 'healthy' both land in the table as separate labels. Report the average check latency for each health status, treating casing differences as the same state, with the slowest states listed first. ## Worked solution and explanation ### What this is really asking This looks like a plain average-per-group, and the arithmetic is. The real problem is dirty enum casing: `status` was written by different agents, so the same state arrives as 'Healthy', 'healthy', and 'DEGRADED'. Group on the raw column and each casing becomes its own bucket, so 'healthy' averages three rows while 'Healthy' averages a different two, and both numbers are computed on half the evidence. The skill being probed is recognizing that `status` is untrusted text and folding it to one canonical form before you aggregate. --- ### Break down the requirements #### Step 1: Fold the casing first Apply LOWER(status) (or UPPER) and group on that expression, not on the raw column. This is what makes 'Healthy' and 'healthy' land in the same bucket. Do it in the GROUP BY key itself so the projected label matches the grouping. #### Step 2: Average each state Project AVG(latency) as avg_latency per folded status. AVG skips the null latency on the failed cache-svc check automatically, so the degraded mean is taken over the two rows that actually have a number. #### Step 3: Slowest first Order by avg_latency descending so the slowest states surface first, which is the gradient the SRE team is chasing. --- ### The solution **AVERAGE LATENCY BY NORMALIZED STATUS** ```sql SELECT LOWER(status) AS status, AVG(latency) AS avg_latency FROM svc_health GROUP BY LOWER(status) ORDER BY avg_latency DESC ``` > **Common Pitfall** > > Grouping on raw `status` instead of a folded key. The query still runs and still looks right, but 'Healthy' and 'healthy' split into two rows, so your averages are computed on partial data and quietly disagree with reality. Nothing errors; the number is just wrong. > **Interviewers Watch For** > > Did you notice the mixed casing at all, or did you trust the column was clean? Spotting that 'DEGRADED' and 'degraded' are the same state, and normalizing before grouping, is the tell that you treat enum columns as dirty input rather than gospel. > **Cost Analysis** > > 8M rows, one scan. LOWER is a cheap per-row scalar, and the aggregate's memory stays proportional to the handful of distinct states, not the row count. If casing normalization becomes hot, a computed/functional index on LOWER(status) lets the engine avoid re-folding on every scan. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - The failed checks have null latency. Is skipping them the right call? _(Tests whether the candidate treats AVG's null-skipping as a decision. Nulls likely mark failed checks; excluding them understates the pain, so decide between exclusion, zero, or a timeout ceiling before reporting.)_ - How would you stop the casing drift at the source instead of cleaning it in every query? _(Means the real fix is upstream: a CHECK constraint or a normalize-on-write step so status is stored canonically, rather than folding at read time forever.)_ - How would you split this by region in the same pass? _(Add region to both SELECT and GROUP BY(alongside the folded status). Then you can compare the same state across regions and rank the worst offenders.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/under_the_same_name) - [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.