Resolved vs Unresolved Alerts

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

Show, for every service in alert_events, how many of its alerts were resolved and how many are still unresolved, side by side, the way the on-call team's reliability review reads it. Resolution is judged by the recorded resolution time: an alert without one was never cleared and counts as unresolved.

Resolved versus open. By severity.

Tables

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

Expected output

Worked solution and explanation

What this problem is really testing

Strip the on-call costume and this is a one-pass pivot over a single nullable column. For each svc_name you fold resolved into two counts: alerts that carry a resolution time and alerts that do not. Anyone can write the SUM(CASE ...). The whole question is the 66 rows where resolved is NULL: compare the raw column and those open alerts slide into the ELSE branch, so every service reports zero unresolved alerts and looks perfectly healthy.

Building it up

Step 1: Turn the nullable timestamp into a clean open-or-closed test

COALESCE(resolved, '') = '' is true exactly when no resolution time exists, whether the column holds NULL or a blank string. Settle this before writing either count, because it decides where every open alert goes.

Step 2: Split into two columns with conditional sums

SUM(CASE WHEN COALESCE(resolved, '') = '' THEN 1 ELSE 0 END) counts the open alerts; flip THEN and ELSE for the resolved ones. One scan of alert_events fills both columns, with no self-join and no second query.

Step 3: Read resolution from resolved, not status

status has a 'resolved' label, and it disagrees with the timestamp on plenty of rows: an alert can be 'firing' with a resolution time, or 'resolved' without one. The review counts on the recorded time, so status never enters the query. GROUP BY svc_name then gives one row per service.

The solution

SELECT
  svc_name,
  SUM(CASE WHEN COALESCE(resolved, '') = '' THEN 0 ELSE 1 END) AS resolved_count,
  SUM(CASE WHEN COALESCE(resolved, '') = '' THEN 1 ELSE 0 END) AS unresolved_count
FROM alert_events
GROUP BY svc_name

Naive: compare the raw column

SUM(CASE WHEN resolved = '' THEN 1 ELSE 0 END) gets NULL from the comparison on every open alert, takes the ELSE, and counts none of them as unresolved.

Correct: coalesce first

SUM(CASE WHEN COALESCE(resolved, '') = '' THEN 1 ELSE 0 END) forces the NULL to an empty string, so every open alert is counted. It also catches a blank resolution string, which resolved IS NULL alone would miss.

Common follow-up questions

• What changes if some `resolved` values are blank strings rather than NULL? (Tests whether the candidate sees that `COALESCE(resolved, '') = ''` already covers both.)
• How would you add a third column for alerts that were acknowledged (`ack_by` set) but never resolved? (Extends the conditional-aggregation pattern to a compound condition.)
• If you needed the unresolved rate as a percentage per service, how would you compute it in the same query? (Combines a conditional SUM with a ratio computed in the same pass.)