Service Reliability Tiers

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Airbnb

Problem

Checks taken while a service was flagged as degraded carry distorted latency and stay out of the numbers. Every other check falls into a reliability tier by its uptime: 99.9% and up is 'Platinum', 99.0 to 99.89% is 'Gold', 95.0 to 98.99% is 'Silver', and anything lower, or with no uptime recorded, is 'Bronze'. Report each tier's minimum, average, and maximum latency, highest average latency first.

Reliability tiers. Based on uptime.

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

Strip the monitoring costume and this is a bucket-then-summarize with a poison-row filter hiding in the WHERE. Anyone can write the CASE ladder and the MIN/AVG/MAX. What separates candidates is the exclusion. Degraded checks carry distorted latency, and status stores them in two casings, 'degraded' and 'DEGRADED'. String comparison here respects case, LIKE included, so status != 'degraded' or a lone NOT LIKE 'degraded%' quietly keeps every uppercase row. Those readings flow into the tier numbers: Gold's minimum drops from 84.2 to 53.2, its average sinks by about 70, and nothing about the result looks wrong.

Working to the answer

Step 1: Derive the tier from uptime

No column stores the tier, so you derive it. A CASE ladder over uptime checks the highest cutoff first and falls through. Each branch uses >=, so a service at exactly 99.0 lands in 'Gold', not 'Silver'. The ELSE does real work too. A check whose uptime is NULL matches no branch and falls to 'Bronze', which is what the statement asks for. Dropping those rows is a different answer.

Step 2: Exclude degraded checks in every casing

Read the sample rows before writing the filter: 'degraded' and 'DEGRADED' both appear, and so do 'healthy' and 'Healthy'. Comparisons here are case-sensitive, so one pattern catches one spelling. Exclude both with status NOT LIKE 'degraded%' AND status NOT LIKE 'DEGRADED%', and put that in the WHERE so the poisoned rows never reach an aggregate.

Step 3: Summarize latency per tier

GROUP BY the tier alias and take MIN, AVG and MAX of latency per group. NULL latencies need no handling: aggregates skip NULL, so they are neither counted nor treated as zero. Adding COALESCE(latency, 0) would drag the average down.

Step 4: Order by average latency

The result reads worst-first, so ORDER BY avg_latency DESC puts the most latent tier on top. Sort on the computed average, not on a raw column.

The solution

SELECT
  CASE
    WHEN uptime >= 99.9 THEN 'Platinum'
    WHEN uptime >= 99.0 THEN 'Gold'
    WHEN uptime >= 95.0 THEN 'Silver'
    ELSE 'Bronze'
  END AS tier,
  MIN(latency) AS min_latency,
  AVG(latency) AS avg_latency,
  MAX(latency) AS max_latency
FROM svc_health
WHERE status NOT LIKE 'degraded%'
  AND status NOT LIKE 'DEGRADED%'
GROUP BY tier
ORDER BY avg_latency DESC

Leaks the uppercase rows

status != 'degraded', status NOT IN ('degraded') and a lone NOT LIKE 'degraded%' each match one spelling. Every 'DEGRADED' check stays in its tier.

Covers both spellings

status NOT LIKE 'degraded%' AND status NOT LIKE 'DEGRADED%' removes each casing the data holds, so only clean checks reach MIN, AVG and MAX.

Common follow-up questions

• `latency` is about 1% NULL. How does your query treat those rows across `MIN`, `AVG` and `MAX`, and would `COALESCE(latency, 0)` change the answer? (Tests whether the candidate knows aggregates skip NULLs in `svc_health.latency` rather than counting them.)
• Next week a check arrives with `status` 'Degraded'. Your filter lists two spellings. How would you make the exclusion hold for any casing, and where would you fix it upstream? (Tests case normalization with `LOWER`, engine-dependent `LIKE` semantics and normalizing data on write.)
• A service reports `uptime` of exactly 99.0. Which tier does it land in? How would you change the ladder to put it in 'Silver', and to report checks with no uptime separately? (Tests boundary reasoning and NULL handling on the `CASE` ladder.)