Expose the Next Scheduled Run
Use LEAD to return next_started_at within each pipeline.
- Window functions
- Sorting
Exercise brief
Understand the request
Scheduler reliability engineer Timeline diagnostics need the next observed start timestamp for every pipeline run.
Timeline diagnostics need the next observed start timestamp for every pipeline run. Use LEAD to return next_started_at within each pipeline.
Return
- Return pipeline_name, run_id, started_at, and next_started_at.
- Order by pipeline_name, started_at, and run_id.
Constraints
- Use the same deterministic chronology in LEAD and final output.
- Allow the last row in each partition to return NULL.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
pipeline_nameTEXTrun_idINTEGERstarted_atTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
LEAD reads forward in the window sequence.
Hint 2
Partition by pipeline_name so a successor never crosses pipelines.
Hint 3
Order by started_at and run_id to resolve equal timestamps.
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, LEAD(started_at) OVER (PARTITION BY pipeline_name ORDER BY started_at, run_id) AS next_started_at FROM pipeline_runs ORDER BY pipeline_name, started_at, run_id;Why this works
LEAD is the forward-looking counterpart to LAG. The partition prevents cross-pipeline leakage and the tie-breaker defines a reproducible successor.
Success check
Every run points to its deterministic successor and each pipeline tail is NULL.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | started_at | next_started_at |
|---|---|---|---|
| billing_rollup | 301 | 2026-06-03 08:00:00 | 2026-06-03 09:00:00 |
| billing_rollup | 302 | 2026-06-03 09:00:00 | 2026-06-03 10:00:00 |
| billing_rollup | 303 | 2026-06-03 10:00:00 | 2026-06-03 11:00:00 |
| billing_rollup | 304 | 2026-06-03 11:00:00 | NULL |
| customer_sync | 201 | 2026-06-03 09:30:00 | 2026-06-03 10:30:00 |
| customer_sync | 202 | 2026-06-03 10:30:00 | 2026-06-03 11:30:00 |
| customer_sync | 203 | 2026-06-03 11:30:00 | 2026-06-03 12:30:00 |
| customer_sync | 204 | 2026-06-03 12:30:00 | NULL |
| ingest_orders | 101 | 2026-06-03 09:00:00 | 2026-06-03 10:00:00 |
| ingest_orders | 102 | 2026-06-03 10:00:00 | 2026-06-03 10:00:00 |
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.