Latest Version Per Service

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

L3

Asked in

Meta

Problem

The release dashboard has been skipping every deploy that went to staging or failed, and the on-call engineer found services stuck on builds they have since moved past. Rebuild it from deploy_logs: each service with the version from its most recent deploy and how many seconds that deploy ran.

The latest version deployed. Each service.

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

This is a latest-row-per-group lookup dressed up as a release dashboard. Finding when each service last deployed is one MAX(deploy_at); the skill is getting the version and the duration out of that same row. Anyone can write the GROUP BY. What separates candidates is noticing that MAX(dur_secs) beside it reports the service's slowest deploy ever, not its latest, so the dashboard quietly stitches together numbers from different deploys.

Walking to the answer

Step 1: Keep every deploy in scope

The broken dashboard dropped staging and failed deploys, which is exactly why services looked stuck. No WHERE on env_name or status: a failed rollout to staging is still the last thing that touched the service, and you also avoid the mixed casing ('Production', 'STAGING', 'FAILED') those columns carry.

Step 2: Number each service's deploys by deploy_at, newest first

ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY deploy_at DESC) restarts the count for every service and gives 1 to its newest deploy. deploy_at is a fixed-width 'YYYY-MM-DD HH:MM:SS' string, so descending text order is newest-first time order.

Step 3: Keep number one in the outer query

Filter ranked_deploys to recency = 1. A window result does not exist yet when the same SELECT's WHERE runs, which is why the numbering sits in a derived table and the filter one level up.

SELECT svc_name, version, dur_secs
FROM (
    SELECT svc_name, version, dur_secs,
        ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY deploy_at DESC) AS recency
    FROM deploy_logs
) ranked_deploys
WHERE recency = 1

Summarize each column

SELECT svc_name, MAX(version), MAX(dur_secs) ... GROUP BY svc_name runs and returns one row per service, which is why it looks right. MAX(dur_secs) is the slowest deploy the service ever had, and MAX(version) is the alphabetically largest string, so 'v3.0.0-rc1' would outrank '2.1.1' whenever both appeared.

Pick the newest row

ranked_deploys keeps exactly one row per service, the one with the latest deploy_at, and version and dur_secs are both read from it.

Common follow-up questions

• Two deploys of the same service land on the same `deploy_at`: which one should the dashboard show, and how do you make that choice repeatable? (Tests adding a deterministic tie-breaker such as `log_id` to the window's `ORDER BY`.)
• 'Payment-API' and ' payment-api' show up as separate rows. How would you fold them into one service? (Tests normalizing a dirty key with `TRIM` and `LOWER` before partitioning.)
• The dashboard now also needs the latest successful production deploy per service. What changes? (Tests a case-insensitive filter on `env_name` and `status` before numbering, alongside the unfiltered result.)