Service Uptime Minutes

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

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Samsara

Problem

Find how many full hours each service's still-open alerts in alert_events have been firing as of 2026-09-12, where every alert counts only its own completed hours before they are added up. The reliability team's onboarding review covers alerts that fired no later than 180 days after the service's earliest check in svc_health.

Status changed. How long was it actually up?

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, checked TEXT, status TEXT, latency REAL, uptime REAL, region TEXT

alert_events

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

Expected output

Worked solution and explanation

What this really is

This is an open-interval measurement dressed up as an uptime report. The missing resolved timestamp is not dirty data to clean away: it IS the open alert, the only row the question is about, and its end point is not in the table at all. Anyone can write the per-service MIN(checked) and the join. What separates candidates is whether muscle memory types resolved IS NOT NULL and measures fired-to-resolved, or drops the test and measures everything to the as-of date. Either way every service's total is wrong, the second one by months of hours from incidents fixed long ago.

Building it

Step 1: Anchor each service on its own first check

The review period starts at each service's earliest row in svc_health, so the svc_start CTE takes MIN(checked) per svc_name. A single fleet-wide start date would let late-onboarded services drag in alerts from well past their first 180 days.

Step 2: Inner-join alerts to the anchor

Join alert_events to svc_start on svc_name. Services that alert but were never health-checked (the seed has a few, like 'db-primary') have no period to measure against, and the inner join drops them on purpose.

Step 3: Keep only the open alerts

a.resolved IS NULL is the whole point of the query. It throws away roughly 94% of the alert rows, every one a closed incident the question does not ask about.

Step 4: Cut at 180 days past the anchor

An open alert has no resolution to test, so the window is on fired_at: keep it when a.fired_at <= datetime(s.first_check, '+180 days'). Alerts raised later in the service's life are outside the onboarding review.

Step 5: Measure to the as-of date, then truncate per alert

julianday('2026-09-12') - julianday(a.fired_at) is days elapsed; times 24 is hours. The CAST to INTEGER sits inside the SUM, so each alert drops its partial hour before the service total is built.

WITH svc_start AS (
    SELECT svc_name, MIN(checked) AS first_check
    FROM svc_health
    GROUP BY svc_name
)
SELECT a.svc_name,
       SUM(CAST((julianday('2026-09-12') - julianday(a.fired_at)) * 24 AS INTEGER)) AS open_alert_hours
FROM alert_events a
JOIN svc_start s ON a.svc_name = s.svc_name
WHERE a.resolved IS NULL
  AND a.fired_at <= datetime(s.first_check, '+180 days')
GROUP BY a.svc_name

Habit

WHERE resolved IS NOT NULL and resolved - fired_at: a perfectly good closed-incident report, answering a question nobody asked.

The question

WHERE resolved IS NULL and 2026-09-12 - fired_at: only the alerts still burning, measured to the moment of the review.

Common follow-up questions

• How would you report closed and open alert hours side by side in one row per service? (Tests splitting one table into two measures on the presence of `resolved`.)
• What if `resolved` is sometimes earlier than `fired_at` because of clock skew? (Probes data-quality handling on timestamp arithmetic.)
• The review runs daily. How would you avoid rescanning all of `svc_health` for the anchor every time? (Tests incremental thinking: the earliest check per service never changes once recorded.)