# Nobody Was Watching > Every release shipped clean. Not one had a tripwire set. Find the ones flying blind. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## 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 **Anti-join for unmatched records** ```sql 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 ``` > **Cost Analysis** > > Left join of 2M deploy_logs to 5M alert_events on svc_name, then the NULL filter strips out everything that matched. An index on `alert_events(svc_name)` keeps the probe cheap; without it you pay a full scan of the larger table per service. > **Interviewers Watch For** > > The tell is whether the candidate reaches for LEFT JOIN + IS NULL (intent is obvious) or NOT EXISTS (equivalent, and often the plan the optimizer prefers). Both are honest anti-joins. The candidate who reaches for NOT IN and never mentions nulls is the one to worry about. > **Common Pitfall** > > An INNER JOIN answers the opposite question: deployments that DID alert. It looks almost identical and passes a glance, which is why it slips through. The anti-join needs LEFT JOIN + NULL check, or NOT EXISTS, and nothing less. --- ## 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(*).)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/nobody_was_watching) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.