# Deployments Without Alerts

> Deployed without a single alert. Suspicious or impressive?

Canonical URL: <https://datadriven.io/problems/deployments_without_alerts>

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

### Why this problem exists in real interviews

This tests anti-join patterns (LEFT JOIN with NULL check or NOT EXISTS). Finding records in one table that have no match in another is a fundamental SQL operation.

---

### Break down the requirements

#### Step 1: Left join or anti-join

LEFT JOIN `deploy_logs` to `alert_events` on `svc_name`. Rows with no alert match will have NULL alert columns.

#### Step 2: Filter for no match

`WHERE ae.alert_id IS NULL` keeps only unmatched deployments.

#### Step 3: Sort by recency

`ORDER BY deploy_at DESC` shows most recent first.

---

### 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. The NULL filter reduces the output to unmatched deploys. An index on `alert_events(svc_name)` accelerates the join.

> **Interviewers Watch For**
>
> Whether the candidate uses LEFT JOIN + IS NULL (clear intent) vs NOT EXISTS (equivalent, sometimes faster). Both are correct anti-join patterns.

> **Common Pitfall**
>
> Using an INNER JOIN would return the opposite: deployments WITH alerts. The anti-join pattern requires LEFT JOIN + NULL check or NOT EXISTS.

---

## 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/deployments_without_alerts)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.