Longest Deploy With Full Identifier

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

Visa

Problem

Which deployments in deploy_logs ran within 10 seconds of the longest recorded duration, from longest to shortest? Each one appears as a label naming its service, version and log entry, next to its duration in seconds.

The longest deployment. Full ID.

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 relative-threshold filter dressed up as a deploy report. The cutoff is not a number you are given: it is the longest recorded dur_secs minus 10, and it only exists after the table has been read once. Everyone sorts by duration. What separates candidates is refusing to turn 'near the longest' into a row count: LIMIT 3 lands between the two deployments tied at 606 seconds and keeps one of them arbitrarily, and a cutoff of 598 typed in after peeking at the data is right today and wrong after the next slow release.

Getting there

Step 1: Compute the ceiling once

SELECT MAX(dur_secs) FROM deploy_logs returns one value, 608 on this data. MAX skips rows where dur_secs is NULL, so a deployment with no recorded time can never set the ceiling.

Step 2: Compare every row to the ceiling

Put that scalar subquery in WHERE dur_secs >= (...) - 10. Every row is tested against the same number, so every deployment inside the band survives, ties included, and rows with a NULL duration drop out because the comparison is never true.

Step 3: Build the label from the row itself

svc_name and version repeat across deployments, so the label appends log_id: svc_name || ':' || version || ' (deploy #' || log_id || ')'. That is why the filter has to keep rows rather than collapse them into an aggregate.

Step 4: Sort longest first

ORDER BY dur_secs DESC. Rows that share a duration may come back in either order; only the duration order is part of the answer.

SELECT
  svc_name || ':' || version || ' (deploy #' || log_id || ')' AS full_identifier,
  dur_secs
FROM deploy_logs
WHERE dur_secs >= (SELECT MAX(dur_secs) FROM deploy_logs) - 10
ORDER BY dur_secs DESC;

Top rows by count

ORDER BY dur_secs DESC LIMIT 3 returns three rows because you said three. The band holds four, and the third and fourth tie at 606, so which one survives is up to the engine.

Rows inside the band

WHERE dur_secs >= (SELECT MAX(dur_secs) FROM deploy_logs) - 10 returns however many rows the data puts in the band: four today, more tomorrow if a cluster of slow deploys lands.

SELECT
  svc_name || ':' || version || ' (deploy #' || log_id || ')' AS full_identifier,
  dur_secs
FROM deploy_logs
ORDER BY dur_secs DESC
LIMIT 3;

Common follow-up questions

• How would you return the deployments within 10 seconds of the longest for each service instead? (Moves the ceiling from one global value to one per `svc_name`, and the casing and whitespace variants of `svc_name` start to matter.)
• What changes if the margin is 2 percent of the longest instead of 10 seconds? (Arithmetic on the scalar: `MAX(dur_secs) * 0.98`, and whether the comparison stays numeric.)
• Should rolled-back or failed deployments count toward the longest? (Tests whether the candidate asks before filtering on `status`, whose values come in several casings.)