# Sirens and Smoke

> Stale alerts. Still ringing.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Pull all alert event details (alert ID, service name, severity, status, fired-at time, acknowledged-by, and resolved status) from 2026 where the severity is either 'high' or 'critical' (case-insensitive).

## Worked solution and explanation

### Why this problem exists in real interviews

Interviewers use this alerting scenario to test date extraction for time bucketing against the `alert_events` table. The focus is on how you handle columns like `alert_id`, `svc_name`, and `severity` when building the result.

---

### Break down the requirements

#### Step 1: Filter to the target set

The `IN` list restricts the query to only the specified values, avoiding a full-table scan of irrelevant rows.

#### Step 2: Select the target columns

The SELECT clause picks exactly the columns the prompt asks for. Returning extra columns or missing a required alias would fail the grading check.

---

### The solution

**Filter to the target set to find sirens and smoke**

```sql
SELECT alert_id, svc_name, severity, status, fired_at, ack_by, resolved
FROM alert_events
WHERE LOWER(severity) IN ('high', 'critical') AND strftime('%Y', fired_at) = '2026'
```

> **Cost Analysis**
>
> With ~15M rows, the query performs a single sequential scan. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for how you handle date arithmetic and whether you account for edge cases like month boundaries.

> **Common Pitfall**
>
> Returning extra columns that the prompt did not ask for, or using the wrong column alias, causes a grading mismatch even when the logic is correct.

---

## Common follow-up questions

- What result would you get if every value in `alert_events.resolved` were NULL? Would your query return an empty set or something unexpected? _(Tests extreme NULL scenarios and whether the candidate guards against edge cases in `resolved`.)_
- If `alert_events` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `resolved`? _(Tests ability to identify performance hotspots related to `alert_events.resolved` at scale.)_
- If the date column in `alert_events` spans multiple years, does your date extraction logic still produce correct time buckets? _(Tests whether the candidate accounts for year boundaries in date bucketing.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/sirens_and_smoke)
- [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.