Infant Mortality

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Google

Problem

Our svc_health checks record status with inconsistent capitalization, and the casing carries no meaning. For every service whose first check landed on or after January 1, 2026, report its failure rate as a decimal fraction: the share of all its checks that came back as anything other than healthy.

The youngest ones break first.

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 problem really is

This is a per-group cohort test wearing a reliability costume. 'First check on or after January 1, 2026' is a statement about MIN(checked) across a service's whole history, not about any one row. Everyone writes the conditional ratio; what separates candidates is where the date goes. Put checked >= '2026-01-01' in WHERE and every veteran service that also ran this year sails in as new, with its rate computed over this year's checks alone.

Break down the requirements

Step 1: Count failures without trusting casing

SUM(CASE WHEN LOWER(status) <> 'healthy' THEN 1 ELSE 0 END) over COUNT(*). Lowercasing matters on this data: 'Healthy' sits next to 'healthy', and a raw comparison books every capitalized healthy check as a failure.

Step 2: Force fractional division

Cast the sum to REAL before dividing. Integer division turns every rate between 0 and 1 into 0, and the statement asks for a decimal fraction.

Step 3: Scope the cohort on the group

HAVING MIN(checked) >= '2026-01-01' keeps a service only when its whole history starts on or after that date. It has to be HAVING: the minimum does not exist until the group does. No upper bound is needed, because the question is 'new since', and any later first check qualifies.

The solution

SELECT
  svc_name,
  CAST(SUM(CASE WHEN LOWER(status) <> 'healthy' THEN 1 ELSE 0 END) AS REAL)
    / COUNT(*) AS negative_ratio
FROM svc_health
GROUP BY svc_name
HAVING MIN(checked) >= '2026-01-01'

Row filter

WHERE checked >= '2026-01-01' asks whether one check is recent. Every service with a check this year passes, and its rate silently drops all its earlier history.

Group filter

HAVING MIN(checked) >= '2026-01-01' asks whether the service's earliest check is recent. Only genuinely new services pass, and their rate covers every check they have.

Common follow-up questions

Common follow-up questions

• What changes if probes with no recorded `latency` never completed and should be dropped? (Adds a row-level `WHERE latency IS NOT NULL`, which moves both the rates and each service's first-seen date.)
• What if 'new' means first registered in a service catalog, not first seen in `svc_health`? (Tests whether the cohort test moves to a join against a registry table instead of `MIN(checked)`.)
• How would you return only new services failing more than a fifth of their checks? (Adds a threshold on the same expression in `HAVING` and asks about the boundary.)
• Could you prune partitions to this year to make it cheaper? (Checks that the candidate sees why pruning `checked` corrupts the cohort, not just the rate.)