# Top Alert Resolvers

> The engineers who resolve the most.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The incident response team is recognizing top responders. An alert is considered resolved when its status is 'resolved', and the person who acknowledged it (the ack_by field) gets the credit. Exclude alerts with no acknowledger. Return each acknowledger and their count of resolved alerts, sorted from most resolved to least.

## Worked solution and explanation

### Why this problem exists in real interviews

Working with `alert_events`, this problem tests filtering to the top rows after aggregation. The interviewer checks whether you choose the right window function and aggregate to the correct grain before ranking.

---

### Break down the requirements

#### Step 1: Apply filters

Use a `WHERE` clause to narrow the data to the relevant subset before aggregation.

#### Step 2: Aggregate per svc_name

`GROUP BY svc_name` with the appropriate aggregate function produces one summary row per group from the `alert_events` table.

#### Step 3: Rank the results

`ORDER BY` the aggregate descending with `LIMIT` to surface the top entries.

---

### The solution

**Count resolved alerts grouped by ack_by from alert_events**

```sql
SELECT
    svc_name,
    SUM(resolved) AS total_resolved
FROM alert_events
GROUP BY svc_name
ORDER BY total_resolved DESC
LIMIT 10
```

> **Cost Analysis**
>
> The GROUP BY reduces the 20M-row `alert_events` table to the number of distinct `svc_name` values. A covering index on `(svc_name, resolved)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers verify you aggregate before sorting. Sorting raw rows gives per-row values, not group totals. The correct grain is one row per `svc_name`.

> **Common Pitfall**
>
> Using the wrong aggregate function. `SUM` gives totals, `COUNT` gives volume, `AVG` gives rates. Read the prompt to determine which metric is needed.

---

## Common follow-up questions

- If ack_by is NULL for some resolved alerts (auto-resolved), does your query inadvertently create a NULL group? _(Tests whether the candidate adds WHERE ack_by IS NOT NULL to exclude unattributed resolutions.)_
- How would you also show each resolver's average time-to-resolution using fired_at and resolved timestamps? _(Tests ability to add a computed aggregate (AVG of timestamp difference) to the same grouped query.)_
- If the same alert_id appears multiple times with different statuses, how do you ensure you count only the final resolution? _(Tests deduplication awareness; a subquery to pick the latest status per alert_id may be needed.)_

## Related

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