# Infant Mortality > The youngest ones break first. Canonical URL: 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. ## Tables ### svc_health - check_id INTEGER - svc_name TEXT - status TEXT - latency REAL - uptime REAL - checked TEXT - region TEXT Sample rows (10 of 200): | check_id | svc_name | status | latency | uptime | checked | region | | --- | --- | --- | --- | --- | --- | --- | | 137 | payment-api | degraded | 3.6 | 97.03 | 2026-02-02 01:05:00 | us-west-2 | | 174 | user-svc | unhealthy | 6.7 | 94.06 | 2026-03-03 02:10:00 | eu-west-1 | | 211 | search-api | timeout | 9.8 | 91.09 | 2026-04-04 03:15:00 | ap-south-1 | | 248 | gateway | Healthy | 12.9 | 98.12 | 2026-05-05 04:20:00 | eu-central-1 | | 285 | notif-svc | DEGRADED | 16 | 95.15 | 2026-06-06 05:25:00 | us-east-1 | | 322 | ml-serving | healthy | 19.1 | 92.18 | 2026-07-07 06:30:00 | us-west-2 | | 359 | cache-svc | degraded | NULL | 99.21 | 2026-08-08 07:35:00 | eu-west-1 | | 396 | queue-worker | unhealthy | 25.3 | 96.24 | 2026-09-09 08:40:00 | ap-south-1 | | 433 | cron-svc | timeout | 28.4 | 93.27 | 2026-01-10 09:45:00 | eu-central-1 | | 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 | ## Expected output First 5 of 7 rows: | svc_name | negative_ratio | | --- | --- | | auth-svc | 0.6111111111111112 | | edge-auth-svc | 0.5 | | event-bus | 0.5 | | feature-flag-svc | 0.5 | | notif-svc | 0.6666666666666666 | ## 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 **Cohort by earliest check** ```sql 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. > **Row facts filter rows, group facts filter groups** > > Sort every condition into per-row or per-group before writing a line. The first-seen date describes the service, so it is `HAVING`. Misfile it and the cohort fills with veterans. > **The cohort test forbids partition pruning** > > At 50M rows partitioned by `checked`, pruning to this year is tempting and incorrect: you need each service's full history to know whether it is new. `GROUP BY svc_name` over about 150 keys stays an in-memory hash aggregate, so the full scan is the honest cost. > **Two questions decide the answer** > > Strong candidates ask whether 'new' means first check or any check, and whether status casing carries meaning. Asking both before typing is the tell. --- ### 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/infant_mortality) - [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.