Service With Most Critical Alerts

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

City and County of San Francisco

Problem

Severity labels in alert_events were never normalized, so any label containing 'critical', in any casing, marks a critical alert, and the service with the most of them is the one the incident commander is triaging. Return every alert that service has fired, critical or not, exactly as it was logged, with each row also carrying that service's critical alert count.

One service keeps setting off the alarms.

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

Two predicates, one costume

This is a two-predicate problem dressed up as incident triage. One predicate decides who wins: only alerts whose severity contains 'critical', in any casing, count toward the leader. A different predicate decides what you hand back: every alert that service fired, at any severity. Anyone can count critical alerts per service. What separates candidates is refusing to let that filter leak into the final rows. Leak it and the commander's view silently loses every 'medium' and 'high' alert from the service that is on fire.

There is a second, quieter test. Each row also carries the service's critical count, and that number has to come from the critical subset, not from a plain count of the service's alerts. The two numbers look alike in a preview until you check them against the rows you returned.

Step 1: Count critical alerts per service

Filter alert_events with severity ILIKE '%critical%' so 'Critical' and 'critical' land in one bucket, then GROUP BY svc_name with COUNT(*). An exact severity = 'critical', or a case-sensitive LIKE, misses the capitalized labels, so the count comes back short and can even point at the wrong service.

Step 2: Keep only the leader in top_service

Sort the counts highest first and LIMIT 1 inside the CTE. That ORDER BY is a selection tool, not presentation: it decides which single row survives the cut, and the final rows carry no promised order.

Step 3: Go back to the full table for the rows

The outer query reads alert_events again with only ae.svc_name IN (SELECT svc_name FROM top_service). No severity predicate here, and no rewriting of the labels: each alert comes back as it was logged. The scalar (SELECT critical_alerts FROM top_service) stamps the count onto every row, so the commander sees the whole history and the number that put this service on top.

WITH top_service AS (
    SELECT svc_name, COUNT(*) AS critical_alerts
    FROM alert_events
    WHERE severity ILIKE '%critical%'
    GROUP BY svc_name
    ORDER BY critical_alerts DESC
    LIMIT 1
)
SELECT
    ae.alert_id,
    ae.svc_name,
    ae.severity,
    ae.status,
    ae.fired_at,
    ae.ack_by,
    ae.resolved,
    (SELECT critical_alerts FROM top_service) AS critical_alerts
FROM alert_events ae
WHERE ae.svc_name IN (SELECT svc_name FROM top_service)

Filter leaks into the output

Repeating severity ILIKE '%critical%' in the outer WHERE returns only the leader's critical alerts. The row count drops, the 'medium' firing alert vanishes, and the result still looks plausible.

Filter stays in the ranking

The ILIKE sits only inside top_service. The outer query filters on svc_name alone, so every alert the service fired comes back, each tagged with critical_alerts.

SELECT ae.*
FROM alert_events ae
WHERE ae.severity ILIKE '%critical%'
  AND ae.svc_name IN (SELECT svc_name FROM top_service)

Common follow-up questions

• What if two services tie for the most critical alerts? (Tests whether the candidate sees that `LIMIT 1` picks arbitrarily and can switch to returning every tied leader.)
• How would you return the full alert history for the top three services instead of one? (Probes whether the CTE generalizes cleanly and whether `critical_alerts` then needs a join rather than a scalar subquery.)
• `alert_events` is partitioned by `fired_at`. How would you limit the scan to the current incident window? (Checks partition pruning awareness and whether the window belongs in the ranking, the output, or both.)