# Service Alert Frequency

> How often does each service trigger alerts?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The incident response team is prioritizing which services need the most reliability investment. Show each service alongside its alert count and its rank by volume, sorted from most to least.

## Worked solution and explanation

### Why this problem exists in real interviews

The core skill being tested is DENSE_RANK for gapless ranking, applied to the `alert_events` table in a alerting context. Getting the `svc_name` column right is where most candidates slip.

---

### Break down the requirements

#### Step 1: Rank results with a window function

`DENSE_RANK()` assigns consecutive ranks without gaps. Unlike `ROW_NUMBER()`, tied values share the same rank, which matters when the prompt asks for ties.

#### 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

**Dense-rank for service alert frequency**

```sql
SELECT svc_name, COUNT(*) AS occurrence_count,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM alert_events
GROUP BY svc_name
ORDER BY occurrence_count DESC
```

> **Cost Analysis**
>
> With ~15M rows, the window function runs on the reduced set after filtering and grouping. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for which ranking function you choose and whether you can explain the difference between `ROW_NUMBER`, `RANK`, and `DENSE_RANK`.

> **Common Pitfall**
>
> Using `ROW_NUMBER()` when the prompt requires tie inclusion silently drops tied rows. Read the prompt for language about ties or 'include all'.

---

## 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 10,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.)_
- What is the default window frame for your window function, and would explicitly setting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? _(Tests knowledge of implicit vs explicit window frame specifications.)_

## Related

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