# Daily Metric Percentage Change

> Yesterday versus today. What moved?

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

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

After a deploy incident, the release manager wants to see day-over-day deployment velocity changes per service. For each service and deploy date, compute the percentage change in deployment count from the previous day. Exclude the first day for each service since there's no baseline. Round to 2 decimal places.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests LAG for percentage change computation with NULL handling. The requirement to exclude the first day per service (no baseline) probes window function edge cases.

---

### Break down the requirements

#### Step 1: Count deploys per service per day

`GROUP BY svc_name, deploy_at::DATE` with `COUNT(*)`.

#### Step 2: Compute previous day count with LAG

`LAG(deploy_count) OVER (PARTITION BY svc_name ORDER BY day)` gives the baseline.

#### Step 3: Calculate percentage change

`ROUND(100.0 * (current - previous) / previous, 2)`. Exclude rows where previous is NULL (first day).

---

### The solution

**Partitioned percentage change with LAG**

```sql
WITH daily AS (
    SELECT
        svc_name,
        deploy_at::DATE AS day,
        COUNT(*) AS deploy_count
    FROM deploy_logs
    GROUP BY svc_name, deploy_at::DATE
),
with_lag AS (
    SELECT
        svc_name,
        day,
        deploy_count,
        LAG(deploy_count) OVER (PARTITION BY svc_name ORDER BY day) AS prev_count
    FROM daily
)
SELECT
    svc_name,
    day,
    ROUND(100.0 * (deploy_count - prev_count) / prev_count, 2) AS pct_change
FROM with_lag
WHERE prev_count IS NOT NULL
ORDER BY svc_name, day
```

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

> **Interviewers Watch For**
>
> Whether the candidate excludes the first day 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 day) 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 days 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/daily_metric_percentage_change)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.