# Service With Most Critical Alerts

> One service keeps setting off the alarms.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

During an active incident, the incident commander needs full details for the service that has received the most alerts where severity contains the word 'critical'. Return all columns for every alert belonging to that service, ordered by fired_at.

## Worked solution and explanation

### Why this problem exists in real interviews

This challenge asks you to apply pattern matching with LIKE to the `alert_events` table, simulating a real alerting workflow. Pay attention to the `severity` column as they drive the aggregation and output.

> **Trick to Solving**
>
> Complex queries with multiple transformation stages are best solved by writing one CTE per stage.
> 
> 1. Identify the intermediate results needed
> 2. Write each as a named CTE
> 3. Chain them: each CTE reads from the previous one
> 4. The final SELECT assembles the output

---

### Break down the requirements

#### Step 1: Isolate the intermediate result in a CTE

The `critical_counts` CTE computes the intermediate aggregation that the outer query builds on. This separation keeps each layer focused on a single task.

#### Step 2: Filter with pattern matching on `severity`

The `LIKE '%critical%'` pattern narrows the result to matching rows. The `%` wildcard matches any sequence of characters.

---

### The solution

**Pattern-match for service with most critical alerts**

```sql
WITH critical_counts AS (
    SELECT svc_name, COUNT(*) AS critical_count
    FROM alert_events
    WHERE severity LIKE '%critical%'
    GROUP BY svc_name
    ORDER BY critical_count DESC
    LIMIT 1
)
SELECT ae.*
FROM alert_events ae
WHERE ae.svc_name IN (SELECT svc_name FROM critical_counts)
ORDER BY ae.fired_at
```

> **Cost Analysis**
>
> With ~30M rows, the GROUP BY reduces the working set before any downstream operations; CTEs materialize intermediate results, which can be beneficial or costly depending on the engine. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether you decompose the problem into named, testable stages rather than nesting everything; whether you use a subquery or self-join, and can explain the tradeoffs.

> **Common Pitfall**
>
> Using LIMIT without ORDER BY returns an arbitrary subset. Always pair LIMIT with a deterministic ORDER BY.

---

## 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`.)_
- With 22,000,000 distinct values in `alert_events.resolved`, how would a composite index on the GROUP BY columns change the execution plan? _(Probes understanding of how cardinality in `resolved` affects grouping and sort operations.)_
- Your LIKE pattern may prevent index usage on `alert_events`. How would you restructure the filter to be index-friendly? _(Tests understanding of leading-wildcard LIKE and its impact on index scans.)_

## Related

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