Slow 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

LinkedIn

Problem

Every row of deploy_logs is one deployment, and its environment tag is typed by hand, so only the exact lowercase 'production' tag marks the real production line. List the production deployments that ran longer than 150 seconds, longest-running first.

Production deploys that took way too long.

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 two-predicate scope dressed up as a deploy audit. Everyone writes the 150-second threshold. What separates candidates is reading 'production deployments' as a constraint on one specific column, env_name, rather than a synonym for the whole of deploy_logs. env_name and status are both short, hand-typed categorical columns, so it is easy to grab the wrong one or skip the scope entirely. Either way the query runs clean and returns slow staging, dev and canary deploys next to the production ones, and the 20-row answer quietly becomes something else.

Step 1: Scope to the production line on env_name

The environment lives in env_name, not status. status records how a deploy ended ('success', 'failed'), and an audit of slow deploys wants them whatever the outcome, so status gets no predicate at all. The tag is typed by hand and the line is drawn at the exact lowercase 'production': rows tagged 'Production' are a different label and stay out, so a plain equality is the whole rule.

Step 2: Apply the 150-second threshold

dur_secs > 150 is strict: a deploy at exactly 150 seconds is not long. Roughly one row in seven has no recorded duration, and NULL > 150 is unknown, so those rows fall out with no extra code. That is the right call: a deploy with no captured duration cannot be shown to have run long.

Step 3: Project four columns, longest first

The preview fixes the shape: svc_name, version, dur_secs, deploy_at, nothing else. Sort on dur_secs DESC. Several deploys share a duration on this data. The audit only asks that longer runs come first, so equal durations may land in either order and need no extra sort key.

SELECT svc_name, version, dur_secs, deploy_at
FROM deploy_logs
WHERE env_name = 'production'
  AND dur_secs > 150
ORDER BY dur_secs DESC

Threshold only

WHERE dur_secs > 150 keeps slow deploys from every environment, hand-typed variants like 'Production' and 'STAGING' included: several times as many rows as the audit asked for.

Scoped and thresholded

WHERE env_name = 'production' AND dur_secs > 150 keeps only the lowercase production line and the long runs: exactly the 20 rows the audit expects.

Common follow-up questions

• What changes if rows tagged 'Production' should also count as production? (Tests normalizing a hand-typed categorical before filtering, and checking what else the change pulls in.)
• Some deploys have a `NULL` `dur_secs`. How would you report them separately instead of dropping them? (Tests knowing that the threshold drops `NULL` silently, and surfacing those rows with an explicit `IS NULL` branch.)
• If `deploy_logs` held billions of rows partitioned by `deploy_at`, how would you keep this audit cheap? (Tests partition pruning with a date window plus an index on `(env_name, dur_secs)`.)