# Still Climbing > Which services kept gaining month over month? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 ## Problem As the first quarter wraps up, the SRE team is comparing each service's March 2026 health-check volume against February. Report each service's growth as its March check count divided by its February count. ## Worked solution and explanation ### What this problem really is This is a two-slice pivot dressed up as a growth metric. The real question: can you fold February and March into two separate per-service counts in one pass, then divide them without letting a service that showed up in only one of the two months blow up the query? Anyone can GROUP BY svc_name. The separator is putting the CASE inside SUM so each month becomes its own column, and guarding the divide with NULLIF so a service with zero February checks returns a clean NULL instead of a divide-by-zero. Get that wrong and the March-only services either vanish from the result or return garbage. --- ### How to get there #### Step 1: Scope to the two months You only care about two year-months: February and March. Filtering checked to those two values with an IN list keeps the scan off the other ten months of partitions, which matters on a 20M-row table. #### Step 2: Count each month with CASE inside SUM One SUM(CASE WHEN month = March THEN 1 ELSE 0 END) counts March rows; a second one counts February rows. Both are computed in the same scan, one row per service. This is the move interviewers are actually probing: pivoting two buckets into two columns without two queries or a self-join. #### Step 3: Divide, safely The ratio is March over February. Cast to a floating type so you get 2.0 rather than an integer-truncated 2, and wrap the February count in NULLIF(..., 0) so a service that had no February checks divides into NULL instead of erroring or dropping out. --- ### The solution **Two-month pivot with a safe divide** ```sql SELECT svc_name, CAST(SUM(CASE WHEN strftime('%Y-%m', checked) = '2026-03' THEN 1 ELSE 0 END) AS DOUBLE) / CAST(NULLIF(SUM(CASE WHEN strftime('%Y-%m', checked) = '2026-02' THEN 1 ELSE 0 END), 0) AS DOUBLE) AS growth_rate FROM svc_health WHERE strftime('%Y-%m', checked) IN ('2026-02', '2026-03') GROUP BY svc_name ``` > **Trick to solving** > > The NULLIF is the whole game. Drop it and any service that appeared only in March triggers a divide-by-zero on the February count. Keep it and that service surfaces with growth_rate NULL, which is the honest answer: you cannot compute a growth rate off a zero baseline. > **Common pitfall** > > Writing CASE WHEN ... THEN SUM(x) instead of SUM(CASE WHEN ... THEN x). Wrapping the aggregate in a CASE evaluates the condition once for the whole group and changes the meaning entirely. The CASE must sit inside the SUM so it is decided per row. > **Interviewers watch for** > > Forgetting the float cast is the silent one. Integer division of two counts truncates, so a service that tripled reads back as 3 while one that grew 50% reads back as 1. The CAST to a floating type is what makes the ratio mean anything. > **Why it stays cheap** > > With ~20M rows partitioned by checked, the IN filter on two year-months prunes the scan to roughly two partitions before the GROUP BY ever runs. The aggregate then collapses to one row per service (~100 groups), so the working set downstream is tiny. --- ## Common follow-up questions - Instead of a single February-to-March step, suppose the team wants month-over-month growth for every consecutive pair of months this year. How would you restructure this? _(Tests whether the candidate can generalize a fixed two-month pivot into a rolling window.)_ - A service that launched in March has no February baseline and comes back NULL. Is that the right behavior for a growth dashboard, or would you represent brand-new services differently? _(Tests reasoning about the NULL baseline case and how it should surface to consumers.)_ - If February rows can still land days after the month closes, how would you keep this metric correct without re-scanning both months every run? _(Tests understanding of incremental aggregation against late-arriving data.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/still_climbing) - [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.