Still Climbing

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

L4

Asked in

Instacart

Problem

Health-check volume in svc_health jumped from February into March 2026, and the SRE team wants each service's March share of its checks across those two months, as a whole percentage with any fraction dropped. A service with no February checks was only switched on in March, so its share comes back blank rather than a full 100.

Which services kept gaining month over month?

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 two-bucket pivot dressed up as an SRE growth question, with two type decisions hiding in the last line. Folding February and March into one row per service is the part everyone gets. What separates candidates is the share itself: a service whose probe first fired in March has zero February checks, so its March share is a perfect 100 and it tops the board as the biggest climber when it never climbed at all. Blank it with NULLIF, then cut the share to a whole number with CAST, not a round. Skip the first and new probes look like breakouts; skip the second and every share with a fraction above one half lands a point high.

How to get there

Step 1: Scope to the two months

Only two year-months matter, so the WHERE keeps rows whose strftime('%Y-%m', checked) is in that two-value IN list. That also fixes the grain: a service shows up exactly when it was checked in at least one of the two months, and every check counts, whatever its status says.

Step 2: Count each month with CASE inside SUM

SUM(CASE WHEN ... = '2026-02' THEN 1 ELSE 0 END) counts February rows and its twin counts March, both in the same scan, one row per service. No second pass, no self-join.

Step 3: Let a missing baseline poison the denominator

The denominator is March plus February. Wrap the February count in NULLIF(..., 0) and a service with no February checks gets a NULL addend, and NULL plus anything is NULL, so its share comes back blank instead of 100. Services with a real baseline divide exactly as before.

Step 4: Multiply first, then drop the fraction

100.0 * march / total keeps the arithmetic in floating point and puts the scaling before the division, so exact shares stay exact. CAST(... AS INTEGER) then drops the fraction: 66.67 becomes 66.

SELECT svc_name,
       CAST(
           100.0 * SUM(CASE WHEN strftime('%Y-%m', checked) = '2026-03' THEN 1 ELSE 0 END)
           / (SUM(CASE WHEN strftime('%Y-%m', checked) = '2026-03' THEN 1 ELSE 0 END)
              + NULLIF(SUM(CASE WHEN strftime('%Y-%m', checked) = '2026-02' THEN 1 ELSE 0 END), 0))
       AS INTEGER) AS march_share_pct
FROM svc_health
WHERE strftime('%Y-%m', checked) IN ('2026-02', '2026-03')
GROUP BY svc_name

Rounded

ROUND(100.0 * march / total): 2 of 3 checks in March comes back as 67, a point above what the team asked for.

Truncated

CAST(100.0 * march / total AS INTEGER): 2 of 3 comes back as 66, the whole percentage with the fraction dropped.

Common follow-up questions

• The team now wants the March share for every region separately. What changes, and which services newly come back blank? (Tests whether the candidate sees that a finer grain creates more empty February buckets.)
• How would you produce this share for every consecutive pair of months this year without one `CASE` per month? (Tests moving from a fixed pivot to a per-month count plus a lag.)
• If February checks can land days after the month closes, how do you keep this figure correct without rescanning both months on every run? (Tests incremental aggregation against late-arriving data.)