Flag Runtime Regressions Against the Previous Run
Compute previous_duration_seconds with LAG, then keep regressions in an outer query.
- Window functions
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Data reliability engineer An alert should fire only when a pipeline run is slower than its immediate predecessor.
An alert should fire only when a pipeline run is slower than its immediate predecessor. Compute previous_duration_seconds with LAG, then keep regressions in an outer query.
Return
- Return pipeline_name, run_id, duration_seconds, and previous_duration_seconds.
- Order by pipeline_name and run_id.
Constraints
- Sequence each pipeline by started_at and run_id.
- Do not filter before calculating LAG.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
pipeline_nameTEXTrun_idINTEGERstarted_atTEXTduration_secondsINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Window functions are evaluated after WHERE at the same query level.
Hint 2
Calculate the predecessor first, then filter the derived result.
Hint 3
Compare duration_seconds > previous_duration_seconds in the outer query.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH sequenced AS (SELECT pipeline_name, run_id, started_at, duration_seconds, LAG(duration_seconds) OVER (PARTITION BY pipeline_name ORDER BY started_at, run_id) AS previous_duration_seconds FROM pipeline_runs) SELECT pipeline_name, run_id, duration_seconds, previous_duration_seconds FROM sequenced WHERE duration_seconds > previous_duration_seconds ORDER BY pipeline_name, run_id;Why this works
A two-step query protects the sequence population. Filtering rows before LAG would change which run counts as the predecessor.
Success check
Only the two true predecessor regressions are returned.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | duration_seconds | previous_duration_seconds |
|---|---|---|---|
| customer_sync | 202 | 35 | 30 |
| ingest_orders | 104 | 80 | 45 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
Open the interactive workspace and practice across SQL topics.