# Deploy Velocity

> Days between deploys. Some services ship fast, others crawl.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

The release manager is benchmarking how often each service ships. For each service, look at the gaps in days between consecutive deploys and take the average. Show the service name and its average gap.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests LAG for computing inter-event gaps. Measuring the average time between consecutive deployments probes window function fluency and interval arithmetic.

---

### Break down the requirements

#### Step 1: Compute gaps between consecutive deploys

`deploy_at - LAG(deploy_at) OVER (PARTITION BY svc_name ORDER BY deploy_at)` gives the gap per deploy.

#### Step 2: Average the gaps per service

`GROUP BY svc_name` with `AVG(gap)` after filtering out NULL first rows.

---

### The solution

**LAG-based deployment frequency analysis**

```sql
WITH gaps AS (
    SELECT
        svc_name,
        deploy_at - LAG(deploy_at) OVER (PARTITION BY svc_name ORDER BY deploy_at) AS gap
    FROM deploy_logs
)
SELECT
    svc_name,
    AVG(gap) AS avg_gap_days
FROM gaps
WHERE gap IS NOT NULL
GROUP BY svc_name
```

> **Cost Analysis**
>
> Window function over 2M rows partitioned by svc_name. Each partition is sorted by deploy_at. The subsequent GROUP BY reduces to the number of services.

> **Interviewers Watch For**
>
> Whether the candidate filters out NULL gaps (first deploy per service has no previous) before averaging. AVG ignores NULLs, so the filter is technically optional but makes intent clear.

> **Common Pitfall**
>
> The gap type depends on the column type. If deploy_at is a timestamp, the difference is an interval; you may need to extract days: EXTRACT(EPOCH FROM gap) / 86400.

---

## Common follow-up questions

- How would you identify services with accelerating deploy frequency? _(Compare recent gaps to older gaps, or compute trend lines.)_
- What if a service has only one deploy? _(All gaps are NULL, so it would not appear in the output.)_
- How would you also show the min and max gap per service? _(Add MIN(gap) and MAX(gap) to the SELECT.)_

## Related

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