Biggest Deployment Decline

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

L4

Problem

An on-call engineer tracing a surge in release cadence is comparing each service's deploys in deploy_logs for 2026 against 2025, counting every logged deploy whatever its status, but only for services that svc_health monitors under exactly the same name. Return the single service whose deploy count grew the most, along with how many more deploys it logged in 2026.

One team's deploy count cratered. Which one?

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

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

This is a year-over-year delta wearing an on-call costume. Anyone can count deploys per service. What separates candidates is two quiet decisions. First, svc_health is a log of checks, not a registry, so joining it raw copies every deploy once per health check and a gain of twelve reads as a gain in the hundreds. Second, once the rows are fenced to 2025 and 2026, every deploy is either a this-year gain or a last-year loss, so the whole change is one signed tally. Skip the fence and three older years of history pour in as losses; sort the wrong way and the service that barely moved wins a contest about acceleration.

Build it in four moves

Step 1: Fence the rows to the two years

WHERE EXTRACT(YEAR FROM deploy_at) IN (2025, 2026). This is not housekeeping: deploy_logs reaches back to 2022, and the tally in step 3 treats anything that is not this year as last year. The filter is the only thing that makes that true.

Step 2: Turn svc_health into a set of names

SELECT DISTINCT svc_name inside a derived table collapses the checks to one row per monitored service, and the inner join keeps only deploys for those names. Off-spellings in deploy_logs such as 'Payment-API' have no exact match and fall away, which is what "monitored under exactly the same name" asks for.

Step 3: Tally signed in one pass

SUM(CASE WHEN year = 2026 THEN 1 ELSE -1 END) per service is this year minus last year directly. A service that deployed in only one of the two years still gets the right number, with no outer join and no COALESCE.

Step 4: Sort by direction, break the tie, take one

ORDER BY deploy_growth DESC, svc_name then LIMIT 1. Descending on the signed value puts the biggest gain first; the alphabetical key only matters if two services grew by the same amount, and then it decides which one the report names.

SELECT
  dl.svc_name AS svc_name,
  SUM(CASE WHEN EXTRACT(YEAR FROM dl.deploy_at) = 2026 THEN 1 ELSE -1 END) AS deploy_growth
FROM deploy_logs dl
JOIN (
  SELECT DISTINCT svc_name
  FROM svc_health
) monitored
  ON monitored.svc_name = dl.svc_name
WHERE EXTRACT(YEAR FROM dl.deploy_at) IN (2025, 2026)
GROUP BY dl.svc_name
ORDER BY deploy_growth DESC LIMIT 1;

Two filtered sums

SUM(CASE WHEN year = 2026 ...) minus SUM(CASE WHEN year = 2025 ...) gives the same numbers, but each sum filters its own year, so it never needs the window and invites you to drop it. Then the scan covers all five years of history for nothing.

One signed tally

WHERE fences the window once, and CASE ... THEN 1 ELSE -1 reads the direction off each row. One expression, one pass, and the filter is visibly load-bearing.

Common follow-up questions

• Return the top 5 growers with their percentage change, not just the count change. (Needs a separate last-year baseline and a guard against dividing by a zero count.)
• 2026 is only partly over. How would you compare it fairly against a full 2025? (Cap last year at the same day of year, or compare deploys per elapsed month.)
• What changes if a service must appear in `svc_health` during the two years specifically? (Moves the membership check into a date-bounded set instead of a global `DISTINCT`.)
• How would you fold 'Payment-API' and ' payment-api' into the monitored service instead of dropping them? (Normalizing the name with `TRIM` and `LOWER` on both sides before the join.)