Healthiest Service Check History

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

Vesta Innovations

Problem

We run scheduled health checks across a fleet of services, and every check logs an uptime score. Find the service or services whose uptime ever reached the highest value recorded anywhere in the table, and take that service's entire check history, not just its top-scoring checks. For every one of its checks show the check time, the time of the immediately preceding check, and the number of days between the two, and because the earliest check has nothing before it, keep that row too with its previous time and its gap left blank.

The healthiest service. Full history.

Tables

svc_health

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

Expected output

Worked solution and explanation

This is a gap-between-consecutive-rows problem hiding behind a service-health leaderboard. There are two moves. First, a global aggregate picks the group: the single highest uptime ever recorded, and every service that touched it. Second, an ordered window walks each chosen service's full timeline. Most candidates get the LAG roughly right. The failure is almost always in the first move. ORDER BY uptime DESC LIMIT 1 quietly keeps one co-champion and deletes the other's entire history, so whole blocks of expected rows vanish and nothing errors.

The trap

Looks right

ORDER BY uptime DESC LIMIT 1 returns one row, so one service. A per-service MAX(uptime) goes wrong the other way and admits every service. Subtracting DATE(checked) values turns a gap of 79.59 days into 79 or 80.

Actually right

uptime = (SELECT MAX(uptime) FROM svc_health) keeps every service tied at the global max. JULIANDAY on the full checked timestamp keeps the fractional day.

Building it

Step 1: Gate on the global max, ties included

The scalar subquery (SELECT MAX(uptime) FROM svc_health) is computed once over the whole table. Filtering on equality with it and applying SELECT DISTINCT svc_name gives a small set of names in healthiest. If two services share the top score, both appear in that set.

Step 2: Take the whole history, not the winning rows

The outer query filters on s.svc_name IN (SELECT svc_name FROM healthiest) and does not filter on uptime. The prompt asks for every check the champion ever logged. Filtering on uptime a second time would shrink each history to its top-scoring checks.

Step 3: Walk each timeline with LAG

LAG(s.checked) OVER (PARTITION BY s.svc_name ORDER BY s.checked) returns the check immediately before the current one within the same service. The query contains two LAG calls. Because both use an identical window, the engine sorts once and evaluates the window a single time.

Step 4: Let the first check stay NULL

The earliest check in each partition has nothing before it, so LAG returns NULL and JULIANDAY(NULL) also yields NULL. The row stays in the output with blank prev_checked and days_diff, which is what the prompt asks for. Adding WHERE prev_checked IS NOT NULL would drop one correct row per service.

WITH healthiest AS (
    SELECT DISTINCT svc_name
    FROM svc_health
    WHERE uptime = (SELECT MAX(uptime) FROM svc_health)
)
SELECT
    s.svc_name,
    s.checked,
    LAG(s.checked) OVER (PARTITION BY s.svc_name ORDER BY s.checked) AS prev_checked,
    JULIANDAY(s.checked)
    - JULIANDAY(LAG(s.checked) OVER (PARTITION BY s.svc_name ORDER BY s.checked)) AS days_diff
FROM svc_health s
WHERE s.svc_name IN (SELECT svc_name FROM healthiest)

Common follow-up questions

• Checks should fire every 15 minutes. How would you flag rows where `days_diff` exceeds that cadence? (Filtering on a window result requires wrapping the query, because `WHERE` runs before the window is evaluated.)
• Two checks share the same `checked` timestamp. What does `LAG` return, and how do you make the result deterministic? (Tests tie-breaking inside `ORDER BY`, for example by adding `check_id`.)
• Only the last 30 days matter. Where does the date bound go, and does it change which service wins? (A bound on the outer query prunes partitions without changing the winner. A bound on the `MAX` subquery changes the question being answered.)