Until Relieved

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

mid

Asked in

NVIDIA

Problem

We're auditing our release cadence, where each successful production deploy of a service stays live until the next successful production deploy of that same service replaces it. The environment and status labels were recorded with inconsistent casing over the years, so match them case-insensitively. For every release that was eventually replaced, report the service, the version, and how many calendar days it stayed live, longest-lived first; when two releases held for the same number of days, list them alphabetically by service and then the earlier deploy first.

Every deploy stands its post until the next one arrives. Find the ones that stood longest.

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 really is

This is a per-partition time-gap problem wearing a DevOps costume. The real question: for each service, how long did each release live before the next one replaced it? That is a LEAD over a service-partitioned timeline, then a date subtraction. Anyone can filter to production. Three things separate candidates here: handling the case-inconsistent env_name and status cleanly before you filter, counting whole calendar days instead of letting the time of day leak into a fractional answer, and recognizing that the most recent release of each service has no successor, so its lifespan is undefined, not zero. Hard-code one casing and you silently drop every 'Production' and 'Success' row, inflating every surviving tenure; subtract raw timestamps and two releases that both spanned 369 dates come back as 369 and 368 depending on the hour they shipped; keep the last release and you invent a NULL-length tenure that sorts wrong.

Break down the requirements

Step 1: Normalize casing, then filter to prod successes

env_name arrives as 'Production', 'production' and status as 'Success', 'success'. Compare on LOWER(...) so all casings survive. A raw env_name = 'production' quietly discards the 'Production' rows, which merges away intermediate deploys and stretches every tenure.

Step 2: Order each service's timeline

PARTITION BY svc_name ORDER BY deploy_at gives each service its own chronological sequence of live releases. The partition is what stops one service's next deploy from being attributed to another.

Step 3: Look ahead to the replacement, count whole days

LEAD(deploy_at) pulls the timestamp of the next deploy for the same service. Subtract on the date portions, julianday(date(next_deploy_at)) minus julianday(date(deploy_at)), so the gap returns as whole calendar days rather than a fraction skewed by the clock time each deploy happened to run. Subtracting raw timestamps is where the 369-vs-368 wobble comes from.

Step 4: Drop the still-live release and sort deterministically

The latest deploy per service has no LEAD value (NULL), meaning it is still live and its tenure is not yet known. Filter next_deploy_at IS NOT NULL, then order by days_live descending. Ties are common because many releases share a lifespan, so break them the same way every run: alphabetically by svc_name, then earliest deploy first, or the row order wobbles between executions.

The solution

SELECT
  svc_name,
  version,
  CAST(julianday(date(next_deploy_at)) - julianday(date(deploy_at)) AS INTEGER) AS days_live
FROM (
  SELECT
    svc_name,
    version,
    deploy_at,
    LEAD(deploy_at) OVER (PARTITION BY svc_name ORDER BY deploy_at) AS next_deploy_at
  FROM (
    SELECT svc_name, version, deploy_at
    FROM deploy_logs
    WHERE LOWER(env_name) = 'production' AND LOWER(status) = 'success'
  ) prod_deploys
) lifespans
WHERE next_deploy_at IS NOT NULL
ORDER BY days_live DESC, svc_name, deploy_at

Common follow-up questions

• How would you also report the current live release per service, the one with no successor? (Tests whether they can surface the NULL-next rows separately rather than discarding them, and compute an open-ended tenure against today.)
• If two deploys of a service share the exact same `deploy_at`, what does LEAD do, and how would you make the ordering deterministic? (Probes tie-breaking inside the window ORDER BY, e.g. adding `log_id` as a secondary sort key.)
• How would you compute the average time a release stays live per service, ignoring the still-live one? (Extends the result into an aggregate over `days_live` grouped by `svc_name`.)