The Vital Signs

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

Airbnb

Problem

How many of our health checks against auth-svc in svc_health landed at each status? Nobody enforced casing when status was written, so 'Healthy' and 'healthy' are the same outcome and count together.

Each check records a verdict. See how they stack up.

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 key-cleaning problem dressed up as an ops tally. Filtering svc_health to auth-svc and counting per status is the part everyone gets right. What separates candidates is noticing that status was written with no casing discipline: 'Healthy' and 'healthy' are one outcome stored two ways, and so are 'DEGRADED' and 'degraded'. Group on the raw string and auth-svc's 18 checks come back as six rows instead of four. Healthy is split 4 and 3 and degraded 3 and 2, so ops reads two half-sized buckets where one real outcome should be.

Build it up

Step 1: Filter on the exact service name

WHERE svc_name = 'auth-svc'. Use equality, not LIKE '%auth%': the table also holds 'edge-auth-svc', a separate service whose four checks would leak into the tally through a substring match.

Step 2: Fold casing inside the grouping key

GROUP BY LOWER(status). The fold has to happen in the key itself, not only in the output column. Lowercasing in the SELECT while grouping on raw status prints tidy labels and still leaves two 'healthy' rows.

Step 3: Count each outcome

COUNT(*) AS check_count. status is never NULL here, so every check lands in a bucket and the four counts sum back to auth-svc's 18 checks. That is a sanity check worth saying out loud.

The solution

SELECT
  LOWER(status) AS status,
  COUNT(*) AS check_count
FROM svc_health
WHERE svc_name = 'auth-svc'
GROUP BY LOWER(status)

SELECT
  status,
  COUNT(*) AS check_count
FROM svc_health
WHERE svc_name = 'auth-svc'
GROUP BY status

Raw spelling

GROUP BY status returns six buckets: 'DEGRADED' 3, 'Healthy' 4, 'degraded' 2, 'healthy' 3, 'timeout' 3, 'unhealthy' 3. Each split outcome looks half as common as it is.

Folded casing

GROUP BY LOWER(status) returns four buckets: 'healthy' 7, 'degraded' 5, 'timeout' 3, 'unhealthy' 3. They still sum to 18, and each outcome appears once.

Common follow-up questions

Common follow-up questions

• Break this down by `region` as well. (Adds `region` to the projection and the grouping; tests whether you keep the casing fold in the key.)
• Restrict to the last 24 hours. (Adds a predicate on `checked`, which finally lets the engine prune partitions.)
• Show each outcome as a share of all auth-svc checks. (Divides each bucket by the total, e.g. `COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()`, over the folded key.)
• What if a padded spelling like ' healthy ' starts showing up? (Tests whether you extend the fold to `LOWER(TRIM(status))` once padding actually appears in the data.)