The Green Light

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

ESPN

Problem

Two things keep a service off our release sign-off: a deploy in deploy_logs still marked in progress, or one marked successful that never recorded how long it took. List every blocked service once, however its name happens to be typed in the log.

Success hides behind a dozen spellings. Find who actually shipped.

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

Strip the release costume and this is a dirty-key cleanup wearing a two-rule filter. The rules read easily: a deploy still 'in_progress' blocks its service outright, and a success blocks it only when dur_secs was never recorded. What separates candidates is noticing that both the label and the key lie. Success is spelled 'success', 'Success' and 'SUCCESS', and analytics qualifies only through a 'Success' row, so an exact match quietly drops it. The in-progress payment-api deploys were typed as 'Payment-API' and 'payment-api ' as well as 'payment-api'. Group on the raw svc_name and the same service comes back three times.

Build it up

Step 1: Write the two blocking rules

A deploy blocks sign-off if status = 'in_progress', or if it succeeded and dur_secs IS NULL. The missing-duration test belongs to the success rule only: an in-progress deploy blocks whether or not it has logged a duration. Parenthesize the success half so a reader sees which conditions travel together.

Step 2: Fold the status casing

'in_progress' has one spelling in this log, but success has three. Compare on LOWER(status) = 'success' so 'Success' and 'SUCCESS' count. That is the only path by which analytics makes the list.

Step 3: Test for a missing duration, not a zero

A duration that was never recorded is NULL. dur_secs = 0 would match a real instant rollout, and dur_secs = NULL matches nothing, so the test is dur_secs IS NULL.

Step 4: Normalize the service name before collapsing

Group on LOWER(TRIM(svc_name)) and return that same expression as service. TRIM alone leaves 'Payment-API' as its own service, and LOWER alone leaves the trailing-space 'payment-api ' as one. Together they give one row per real service.

The solution

SELECT LOWER(TRIM(svc_name)) AS service
FROM deploy_logs
WHERE status = 'in_progress'
   OR (LOWER(status) = 'success' AND dur_secs IS NULL)
GROUP BY LOWER(TRIM(svc_name))

Common follow-up questions

• If a stuck deploy recorded `dur_secs = 0` instead of leaving it `NULL`, would your filter still find it? (Tests whether they separate a missing metric from a real zero.)
• If sign-off should only consider each service's latest deploy, how would you pick that row per service before applying the rules? (Tests moving from any-row logic to a per-key latest record.)
• Could you get the same list with `SELECT DISTINCT` instead of `GROUP BY`? When would you prefer one over the other? (Tests knowledge of alternatives for deduplication.)
• If this check ran every hour, would you keep normalizing `svc_name` and `status` at query time or clean them on write? (Tests where data cleaning belongs in a pipeline.)