Under the Same Name

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

Vesta Innovations

Problem

An on-call engineer's latency breakdown of svc_health lists 'Healthy' and 'healthy' as two different states, because agents write the status in inconsistent letter casing. Treating every casing of a status as the same state, give the average check latency per status, slowest first.

The same state wears different casing. Read the latency behind each one.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a dirty-key cleanup dressed up as a latency report. The average is the easy part; the key is the trap. status in svc_health holds six spellings of four states ('healthy' and 'Healthy', 'degraded' and 'DEGRADED', plus 'unhealthy' and 'timeout'). Grouping on the raw column is case-sensitive, so it returns six rows. The 69 healthy checks split 39 and 30, the 59 degraded checks split 30 and 29, and every one of those averages covers only part of the evidence. Fold the casing before you aggregate and the six labels collapse into the four states the preview shows.

Break down the requirements

Step 1: Fold the casing in the key

Group on LOWER(status), not on status. That expression puts 'Healthy' and 'healthy' in one bucket. Project the same expression as the label, so each output row names one canonical, lowercase state and the label matches the grouping.

Step 2: Average each state

AVG(latency) skips the 23 checks with no latency recorded, so each mean covers only checks that were actually timed: 65 of the 69 healthy ones and 50 of the 59 degraded ones. Counting a missing latency as zero would pull every state's average down.

Step 3: Slowest first

Sort on avg_latency descending. 'timeout' leads at about 401 and 'unhealthy' trails at about 274. The four averages are all different, so the order needs no tie rule.

The solution

SELECT
    LOWER(status) AS status,
    AVG(latency) AS avg_latency
FROM svc_health
GROUP BY LOWER(status)
ORDER BY avg_latency DESC

Raw label

GROUP BY status returns six rows. 'healthy' and 'Healthy' come back as separate states, and each is averaged over only the checks written with its own spelling.

Folded label

GROUP BY LOWER(status) returns four rows, one per real state. Each is averaged over every timed check of that state, whichever agent wrote it.

Common follow-up questions

Common follow-up questions

• The checks with no latency are probably failed checks. Is skipping them the right call? (Tests whether `AVG`'s null-skipping is treated as a decision: excluding untimed checks can understate the pain, so weigh exclusion against a timeout ceiling or a separate failure count.)
• How would you stop the casing drift at the source instead of folding it in every query? (The real fix is upstream: normalize `status` on write, or constrain it to a fixed set of lowercase values.)
• How would you split this by `region` in the same pass? (Add `region` to the select list and to the grouping alongside the folded `status`, so the same state can be compared across regions.)