Compare Each Run with Its Predecessor
Use LAG to read the previous rows_loaded within each pipeline.
- Window functions
- Sorting
Exercise brief
Understand the request
Pipeline observability engineer A run timeline needs prior throughput beside the current run without collapsing source rows.
A run timeline needs prior throughput beside the current run without collapsing source rows. Use LAG to read the previous rows_loaded within each pipeline.
Return
- Return pipeline_name, run_id, started_at, rows_loaded, and previous_rows_loaded.
- Order chronologically by pipeline with run_id as a tie-breaker.
Constraints
- Partition by pipeline_name.
- Order the window by started_at and run_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
pipeline_nameTEXTrun_idINTEGERstarted_atTEXTrows_loadedINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
LAG reads a preceding row without a self-join.
Hint 2
Define both the reset boundary and total chronology.
Hint 3
Use LAG(rows_loaded) over pipeline_name ordered by started_at, run_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT pipeline_name, run_id, started_at, rows_loaded, LAG(rows_loaded) OVER (PARTITION BY pipeline_name ORDER BY started_at, run_id) AS previous_rows_loaded FROM pipeline_runs ORDER BY pipeline_name, started_at, run_id;Why this works
LAG preserves the run grain while exposing an adjacent value. A stable secondary ordering key prevents ambiguous predecessor selection.
Success check
The first run per pipeline has NULL and timestamp ties follow deterministic run_id order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | started_at | rows_loaded | previous_rows_loaded |
|---|---|---|---|---|
| billing_rollup | 301 | 2026-06-03 08:00:00 | 1500 | NULL |
| billing_rollup | 302 | 2026-06-03 09:00:00 | 1500 | 1500 |
| billing_rollup | 303 | 2026-06-03 10:00:00 | 1300 | 1500 |
| billing_rollup | 304 | 2026-06-03 11:00:00 | 1100 | 1300 |
| customer_sync | 201 | 2026-06-03 09:30:00 | 850 | NULL |
| customer_sync | 202 | 2026-06-03 10:30:00 | 850 | 850 |
| customer_sync | 203 | 2026-06-03 11:30:00 | 850 | 850 |
| customer_sync | 204 | 2026-06-03 12:30:00 | 650 | 850 |
| ingest_orders | 101 | 2026-06-03 09:00:00 | 1000 | NULL |
| ingest_orders | 102 | 2026-06-03 10:00:00 | 1200 | 1000 |
Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.
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.