Successful Production Deploys

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Airbnb

Problem

Slow production rollouts are under investigation, and the release team wants every production deployment in deploy_logs that recorded a duration, listed from the longest-running to the shortest. When two ran for exactly the same length, the more recent deploy goes first.

Successful production deploys with duration.

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 asks

Strip the release-team framing and this is a filter on a dirty label wrapped around a null-guarded sort. Anyone can write the WHERE. The three things that separate candidates are all in the data. First, env_name spells production as both 'production' and 'Production', so env_name = 'production' keeps 28 of the 58 deploys that qualify. Second, eight production deploys never recorded a dur_secs, and leaving them in puts untimed rows in a list about how long rollouts took. Third, every production duration shows up exactly twice, so sorting on dur_secs alone leaves each pair in whatever order the engine hands back, while the team asked for the more recent deploy first.

Step 1: Fold the casing before comparing

Compare LOWER(env_name) to 'production' so every spelling of the environment counts once. The sample rows show 'Production' and 'production' side by side. That is the moment to normalize, not after the result comes back short.

Step 2: Drop the untimed deploys

Add dur_secs IS NOT NULL to the same WHERE. A rollout with no timing cannot be placed by length, and the guard has to remove those rows before the sort. You cannot hope the sort will push them out of the way.

Step 3: Sort longest first, newest wins a tie

ORDER BY dur_secs DESC, deploy_at DESC. Duration drives the order and deploy_at decides each tied pair. Because deploy_at is a sortable timestamp string, its descending order is chronological.

SELECT log_id, svc_name, version, env_name, status, deploy_at, dur_secs, author
FROM deploy_logs
WHERE LOWER(env_name) = 'production'
  AND dur_secs IS NOT NULL
ORDER BY dur_secs DESC, deploy_at DESC

Common follow-up questions

• How would you return just the single longest rollout per `svc_name`? (Tests moving from a flat filter to a per-group top-1.)
• If the `env_name` casing is inconsistent upstream, would you normalize on read or fix it at write time? (Tests judgment on where to fix dirty data.)
• If the release team ran this hourly, what index would turn the scan into a seek and still return the rows in order? (Tests the leap from a one-shot scan to a repeatedly run query.)