# The Long Watch > Every release holds until the next one relieves it. Find the ones that held longest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## 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. ## 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)) minus julianday(date(deploy)), 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 **Release tenure via LEAD** ```sql 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 ``` > **Cost Analysis** > > At real scale, imagine deploy_logs at 80M rows over a decade of CI history, partitioned by deploy_at month. The prod-success filter cuts it to a few percent, and the LEAD is a single ordered pass within each svc_name partition, so the plan is one filtered scan plus a sort, no self-join. Pushing a deploy_at date window into prod_deploys lets the planner prune partitions and keeps the sort bounded. > **Interviewers Watch For** > > The tell is whether you handle the case-inconsistent labels with LOWER instead of hard-coding a single casing, whether you count on the date portion so the time of day cannot flip a tenure by a day, and whether you say out loud that the newest release per service is deliberately excluded because its lifespan is still open. Naming those boundary decisions signals you have shipped this kind of report before. > **Common Pitfall** > > Reaching for a correlated self-join (find the MIN deploy_at greater than this one per service) instead of LEAD. It returns the same answer but scans the table once per row and is far slower, and it is easy to break tie handling when two deploys share a timestamp. LEAD expresses the intent directly and the engine does it in one pass. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the-long-watch-production-tenure) - [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.