Deploy Velocity Swings
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
- L5
- Asked in
- Walmart
Problem
Each row of deploy_logs is one deployment of a service, whatever its outcome, timed by deploy_at. List every service's months with that month's deployment count, the count from its most recent earlier month with deployments, and the percentage change between them rounded to 2 decimals, services alphabetically and months oldest first. A service's first month has nothing to compare against, so it is left out.
Month to month, who sped up and who stalled.
Tables
deploy_logs
Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT
| log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author |
|---|---|---|---|---|---|---|---|
| 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob |
| 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie |
| 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana |
| 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve |
| 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank |
| 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice |
| 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB |
| 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice |
| 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob |
| 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie |
Expected output
| svc_name | deploy_month | deploy_count | prev_count | pct_change |
|---|---|---|---|---|
| analytics | 2022-06 | 1 | 1 | 0 |
| analytics | 2022-10 | 1 | 1 | 0 |
| analytics | 2026-03 | 2 | 1 | 100 |
| analytics | 2026-05 | 2 | 1 | 100 |
| analytics | 2026-07 | 3 | 2 | 50 |
Worked solution and explanation
Why this problem exists in real interviews
This is percentage-change velocity dressed up as deployment metrics. The real skill: computing relative change against the prior observation while dropping the first one per service, which has nothing to compare against. Anyone can reach for the window function; the trap is the baseline (each service's first month must be excluded, not zeroed) and the divide-by-zero that lurks when a service had no deploys the previous month. Get the baseline wrong and every service sprouts a bogus first row; miss the zero denominator and the query errors out on real data.
Break down the requirements
Step 1: Count deploys per service per month
Aggregate deploy_logs to deployment count per service per month.
Step 2: Compute previous month count with LAG
Use LAG over (partition by service, order by month) to get the previous month's count.
Step 3: Calculate percentage change
Compute the percentage change from previous to current, excluding rows with no previous month.
The solution
WITH monthly AS (SELECT svc_name, strftime('%Y-%m', deploy_at) AS deploy_month, COUNT(*) AS deploy_count FROM deploy_logs GROUP BY svc_name, deploy_month), with_lag AS (SELECT svc_name, deploy_month, deploy_count, LAG(deploy_count) OVER (PARTITION BY svc_name ORDER BY deploy_month) AS prev_count FROM monthly) SELECT svc_name, deploy_month, deploy_count, prev_count, ROUND((deploy_count - prev_count)*100.0/prev_count,2) AS pct_change FROM with_lag WHERE prev_count IS NOT NULL ORDER BY svc_name, deploy_monthCommon follow-up questions
- What if `prev_count` is zero but current is non-zero? (Percentage change is undefined; NULLIF returns NULL. Discuss how to represent this.)
- How would you identify the service with the most volatile deployment pattern? (Compute STDDEV of percentage changes per service.)
- What if months with zero deployments should also appear? (Tests calendar table join for gap-filling.)