Nobody Was Watching

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

Fidelity

Problem

Which services deployed with no corresponding alert record? Pull every deployment that has no matching alert for its service. Show the service name, version, environment, and deployment status, with the most recent deployment first.

Every release shipped clean. Not one had a tripwire set. Find the ones flying blind.

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

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

Expected output

Worked solution and explanation

What this problem really is

This is a set difference wearing an incident-review costume: which deployments belong to a service that never shows up in the alert table at all. Everyone reaches for a membership check, and that is exactly where it bites. Write NOT IN against a list of alert service names and the moment one alert row carries a NULL service, the whole result quietly collapses to zero rows. Flip to an INNER JOIN and you get the mirror image: deployments that DID alert. Only the LEFT JOIN with alert_id IS NULL survives, and you need both halves. Drop either one and you ship the wrong answer with a straight face.

Break down the requirements

Step 1: Left join on the service

LEFT JOIN deploy_logs to alert_events on svc_name alone. Match on the service, not the version or environment: one alert anywhere for a service knocks out all of its deployments. Unmatched deployments carry NULL alert columns.

Step 2: Filter for no match

WHERE ae.alert_id IS NULL keeps only the rows that found no partner. Filter on a NEVER-NULL alert column like the primary key; a nullable column would let real alerts leak through as false negatives.

Step 3: Sort by recency

ORDER BY deploy_at DESC puts the freshest deployments on top, which is what on-call scans first. Note repeats of the same service each stand as their own row; nothing here deduplicates.

The solution

SELECT dl.svc_name, dl.version, dl.env_name, dl.status
FROM deploy_logs dl
LEFT JOIN alert_events ae ON dl.svc_name = ae.svc_name
WHERE ae.alert_id IS NULL
ORDER BY dl.deploy_at DESC

Common follow-up questions

• What is the performance difference between LEFT JOIN + IS NULL and NOT EXISTS? (NOT EXISTS can short-circuit on the first match; LEFT JOIN must complete the full join. Tests optimizer awareness.)
• What if the join should also match on time window (alert within 1 hour of deploy)? (Add a time condition to the join, making the anti-join more specific.)
• How would you count the number of alert-free deployments per service? (Wrap in a GROUP BY `svc_name` with COUNT(*).)