# Alert Count by Severity Tier

> Alerts by severity. The breakdown matters.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The incident response team needs alert volume broken down by severity tier for the postmortem review. If a severity value is missing, label it as 'unknown'. Present the tiers from most alerts to fewest.

## Worked solution and explanation

### Why this problem exists in real interviews

It looks like a one-liner `GROUP BY` on `alert_events.severity`, and it almost is. The interviewer is checking three things: do you handle `NULL` severities with a label instead of dropping them, do you sort by count rather than by `severity` alphabetically, and do you notice that severity has an implicit business order that the prompt didn't ask for here.

---

### Break down the requirements

#### Step 1: Wrap severity in COALESCE

`COALESCE(severity, 'unknown') AS severity_tier`. A bare `GROUP BY severity` treats `NULL` as its own bucket but renders blank in the result, which fails the 'label it unknown' requirement.

#### Step 2: Count rows per tier

`COUNT(*)` on `alert_events`. Use `*` rather than `COUNT(alert_id)` since the prompt is row volume, not non-null primary keys, and the partition column `fired_at` is irrelevant to the aggregate.

#### Step 3: Group by the labeled alias

`GROUP BY severity_tier` so the `NULL` bucket and the literal `'unknown'` rows (if any exist) collapse into one tier. Grouping by the raw column would split them.

#### Step 4: Order by volume descending

`ORDER BY alert_count DESC`. The prompt says 'most alerts to fewest', so sort the metric, not the tier name. No `LIMIT`: postmortems want the full distribution.

---

### The solution

**ALERT VOLUME BY SEVERITY**

```sql
SELECT
  COALESCE(severity, 'unknown') AS severity_tier,
  COUNT(*) AS alert_count
FROM alert_events
GROUP BY severity_tier
ORDER BY alert_count DESC
```

> **Cost Analysis**
>
> 10M rows, full scan of `alert_events`. Only 4 or 5 distinct severity values, so the hash aggregate is trivial and memory-bound by the group key, not the row count. Partition pruning on `fired_at` does nothing here since the query is unbounded. A covering index on `severity` would let the planner skip the heap entirely.

> **Interviewers Watch For**
>
> Ask out loud: is severity free-text or an enum, and is it case-sensitive? If `'Critical'` and `'critical'` both exist, your group splits and counts are wrong. Naming that risk before writing earns more points than the query itself.

> **Common Pitfall**
>
> Sorting `ORDER BY severity` alphabetically returns critical, high, low, medium, which reads like severity order but isn't. The prompt asks for volume order, so sort by `alert_count DESC`. If business order were required, you'd push severity through a `CASE` expression in the `ORDER BY`.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you sort by business severity order instead of volume? _(Pulls in `CASE WHEN severity = 'critical' THEN 1 ...` in the `ORDER BY`. Tests ordered-enum handling.)_
- What if severity values mix case, like `'High'` and `'high'`? _(Probes whether you reach for `LOWER(severity)` inside the `COALESCE` to canonicalize before grouping.)_
- Same question, but only for the last 24 hours. _(Checks whether you add a `WHERE fired_at >= ...` predicate and recognize partition pruning kicks in.)_
- Add the percentage of total alerts per tier. _(Forces a window function `COUNT(*) OVER ()` or a scalar subquery in the denominator.)_

## Related

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