# Tipping Points

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

Canonical URL: <https://datadriven.io/problems/tipping_points>

Domain: SQL · Difficulty: hard · Seniority: L5

## 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, with the old and new status side by side so the team can trace each transition.

## 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 payment-api's reading to auth-svc's, manufacturing garbage transitions on every row. You have to scope the look-back to one service at a time, ordered inside that scope. Get the partition wrong and every row looks like a change; forget to drop each service's very first reading and you emit a phantom transition against nothing.

> **Trick to solving**
>
> Whenever the ask is to compare a row to its predecessor or successor, that is an offset-window signal. Walk it in this order:
> 
> 1. Decide the direction (previous vs. next)
> 2. Partition by the grouping key (here, svc_name)
> 3. Order by the time column inside that partition (checked)
> 4. Compare the two values in the outer query

---

### 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. LAG returns NULL for each service's first reading, so previous_status IS NOT NULL drops those rows instead of reporting a change against nothing. Then previous_status != current_status keeps the rows where the state actually moved. Note this is a raw, case-sensitive comparison on purpose.

---

### The solution

**Lag over svc_name partitions to surface status flips**

```sql
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 != current_status
```

> **Common pitfall**
>
> The status column arrives in mixed casing: 'Healthy' beside 'healthy', 'DEGRADED' beside 'degraded'. The reflex is to LOWER() it as dirty data, but here the stored value IS the state of record. Fold the casing and you silently erase every Healthy-to-healthy flip the audit exists to catch. The correct move is a raw inequality, so differently-cased values register as distinct states.

> **Interviewers watch for**
>
> Interviewers watch whether you decompose the problem into named, testable stages rather than nesting everything, whether you reach for an offset window function instead of a self-join for row-to-row comparison, and whether you handle the first-row NULL from LAG deliberately rather than letting it leak into the output.

> **Cost analysis**
>
> Across 40M rows this is a single ordered scan: the window function sorts within each svc_name partition once, and the WHERE filter runs on the streamed result. There is no self-join fan-out to blow up. A composite index on (svc_name, checked) lets the engine feed rows to the window already ordered, turning the sort into a cheap merge rather than a full re-sort.

---

## 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 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/tipping_points)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.