Deploy Velocity

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

Problem

The release manager is benchmarking how often each service ships, but a deploy only counts if it logged a run duration: rows with no recorded duration were never instrumented and don't reflect real cadence. For each service, average the gaps in days between its consecutive counted deploys, listed alphabetically by name.

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

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

Expected output

Worked solution and explanation

What this problem is really about

Strip the costume and this is a per-service inter-event gap analysis with a data-quality gate on the front. The skill: reach each service's previous deploy with LAG, but only across the deploys that actually count. The trap is WHERE the filter goes. The uninstrumented rows, the ones with no dur_secs, have to leave the set BEFORE the window runs, so every gap spans two real releases. Filter after the window, or not at all, and LAG measures from a deploy that should not exist, and each service's cadence number comes out wrong.

Walk the requirements

Step 1: Drop the deploys that never counted

Keep only rows where dur_secs is present. A deploy with no recorded duration was never instrumented, so it must leave the set before any gap is measured. Do this in the CTE's WHERE so the window only ever sees counted deploys.

Step 2: Reach the previous qualifying deploy

Within each service, order by deploy_at and use LAG(deploy_at) OVER (PARTITION BY svc_name ORDER BY deploy_at) to pull the prior qualifying deploy's timestamp onto the current row.

Step 3: Turn each pair into days, then average

julianday(deploy_at) - julianday(prev) gives the gap in fractional days. A service's first deploy has no predecessor, so its gap is NULL; AVG skips those on its own, so grouping by svc_name and averaging leaves each service's true cadence.

The solution

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

Common follow-up questions

• How would you flag services whose deploy frequency is accelerating? (Compare recent gaps to older gaps, or fit a trend per service.)
• What happens to a service with only one qualifying deploy? (Every gap is NULL, so AVG returns NULL and the service carries no cadence.)
• How would you also surface the tightest and widest gap per service? (Add MIN(`gap_days`) and MAX(`gap_days`) alongside the AVG in the outer SELECT.)