Tipping Points

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

L5

Asked in

Robinhood

Problem

We run health checks across our services and need to audit unexpected state changes. Surface every reading where a service's status differs from its previous check, showing the old and new status side by side.

A service's health is a story told in how it turns. Find every turn.

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

What this really is

This is a per-service change detector wearing a monitoring costume. The skill being probed: can you line up each reading against the one immediately before it, within the same service, and flag where the two differ? Anyone can eyeball a status column and watch it flip. The trap is ordering. Sort by timestamp across the whole table and you compare a 'payment-api' reading to an 'auth-svc' reading, manufacturing garbage transitions at every hop between services. You have to scope the look-back to one service at a time, ordered inside that scope. Get the partition wrong and almost every row looks like a change; forget to drop each service's very first reading and you emit a phantom transition against nothing.

Break down the requirements

Step 1: Attach each row's prior status in a CTE

Compute the prior status for each reading with LAG(status) OVER (PARTITION BY svc_name ORDER BY checked). Partitioning by svc_name keeps the look-back inside one service so you never compare across services; ordering by checked makes 'previous' mean the immediately earlier reading.

Step 2: Keep only the genuine transitions

The outer query keeps only real transitions. previous_status IS DISTINCT FROM current_status flags any reading whose status differs from the one before it. That comparison treats a NULL previous as different from everything, which is exactly why previous_status IS NOT NULL is load-bearing: without it, every service's first reading (whose LAG is NULL) would surface as a bogus change against nothing. IS DISTINCT FROM is also a raw, case-sensitive comparison, so a 'Healthy' to 'healthy' flip counts. The audit is a set of transitions with no promised order, so there is no outer sort to write.

The solution

WITH with_prev AS (
    SELECT svc_name, checked, status AS current_status, LAG(status) OVER (PARTITION BY svc_name ORDER BY checked) AS previous_status
    FROM svc_health
)
SELECT svc_name, checked, previous_status, current_status
FROM with_prev
WHERE previous_status IS NOT NULL AND previous_status IS DISTINCT FROM current_status

Common follow-up questions

• If `svc_health` has gaps in the `checked` column for certain services, does the `LAG` approach still detect all transitions correctly? (Tests understanding that `LAG` looks at row position, not calendar continuity; gaps can make non-adjacent days appear consecutive.)
• How would you extend this to find runs of three or more consecutive status changes for the same service? (Tests ability to layer window functions or use a running-count technique on the flag column.)
• What happens to the `LAG` value on the very first row per `svc_name` partition, and how does your query handle it? (Tests awareness that `LAG` returns `NULL` for the first row and the need for a filter or `COALESCE`.)
• If two health checks for the same service share the same `checked` timestamp, how would you define which one is 'previous'? (Tests understanding of deterministic ordering; a tiebreaker column like `check_id` is needed in the window's `ORDER BY`.)
• Could you rewrite this without window functions using a self-join, and what are the trade-offs? (Tests flexibility in approach and understanding of self-join performance vs. window scans.)