# Deploy Velocity Swings

> Month to month, who sped up and who stalled.

Canonical URL: <https://datadriven.io/problems/deploy_velocity_swings>

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

After a release retro, the manager wants month-over-month deployment velocity per service. For each service and month, give the percentage change in deployment count from the prior month, leaving out each service's first month since it has no baseline. Round to 2 decimals.

## 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

**Partitioned percentage change with LAG**

```sql
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_month
```

> **Cost Analysis**
>
> Scan of 800K rows aggregated to (services x months). The LAG window function sorts per service partition. Output excludes first months, as specified.

> **Interviewers Watch For**
>
> Whether the candidate excludes the first month per service cleanly (WHERE prev_count IS NOT NULL) rather than using COALESCE with a misleading default value.

> **Common Pitfall**
>
> Dividing by zero when `prev_count` is 0 (zero deploys the previous month) causes an error. Use `NULLIF(prev_count, 0)` in the denominator to produce NULL instead.

---

## Common 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/deploy_velocity_swings)
- [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.