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

The release team is auditing deploy telemetry and wants to catch services that report a clean success yet never log how long the rollout actually took. List each service that has at least one successful deploy with no recorded duration, one entry per service.

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 deploy-audit costume and this is a case-insensitive filter plus a null check, deduped to one row per service. Anyone can write WHERE status = 'success' GROUP BY svc_name. The trap is that both halves are dirty: the seed spells success as 'success', 'Success' and 'SUCCESS', and a completed deploy is one that logged a dur_secs, so the ones you want are the successes where dur_secs is missing. Match status on the raw literal and you silently drop analytics, whose green deploys were capitalized; skip the dur_secs IS NULL test and you list every service that ever shipped instead of the ones with the telemetry gap.

Build it up

Step 1: Fold the status casing

Keep only successful deploys. status is case-mixed in the seed ('success', 'Success', 'SUCCESS'), so compare on LOWER(status) = 'success' to catch every spelling instead of the one that happens to match your literal.

Step 2: Isolate the missing duration

A missing completion time is a NULL, not an empty string and not a zero (a zero would be a real, instant rollout). Isolate the gap with dur_secs IS NULL; writing dur_secs = '' or dur_secs = 0 returns nothing or the wrong rows, because NULL is never equal to anything.

Step 3: Collapse to one row per service

Each service appears across many deploy rows, so collapse them with GROUP BY LOWER(TRIM(svc_name)) to return each service exactly once. The TRIM defends against stray whitespace sneaking into a service name before you group on it.

Step 4: Sort by service

The expected preview is alphabetical, so finish with ORDER BY service to make the result deterministic.

The solution

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

Common follow-up questions

• How would the answer change if a stuck deploy recorded `dur_secs = 0` instead of leaving it `NULL`? Would your filter still be right? (Tests whether they distinguish a missing metric from a real zero.)
• If this audit ran every hour, would you keep folding `status` at query time or normalize it on write, and what are the trade-offs? (Tests awareness that normalization can move to write time.)
• Could you get the same distinct service list with `SELECT DISTINCT` instead of `GROUP BY`? When would you prefer one over the other? (Tests knowledge of alternatives to GROUP BY for deduplication.)
• If `deploy_logs` were partitioned by `deploy_at`, would this query prune partitions or scan them all, and how would you confirm it? (Tests partition pruning versus a full scan.)