Compute a Three-Run Moving Runtime Average
Calculate a three-run moving average per pipeline and round to two decimals.
- Window functions
- Aggregation
- Numeric functions
- Sorting
Exercise brief
Understand the request
Pipeline performance engineer Short-term runtime trend monitoring needs the current run and up to two immediate predecessors.
Short-term runtime trend monitoring needs the current run and up to two immediate predecessors. Calculate a three-run moving average per pipeline and round to two decimals.
Return
- Return pipeline_name, run_id, started_at, duration_seconds, and rolling_3_run_avg.
- Order by pipeline_name, started_at, and run_id.
Constraints
- Use AVG as a window function.
- Declare ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
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
A three-row moving window contains the current row plus two predecessors.
Hint 2
Use ROWS, not a value-based RANGE frame.
Hint 3
Order by started_at, run_id and round the AVG result.
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, duration_seconds, ROUND(AVG(duration_seconds) OVER (PARTITION BY pipeline_name ORDER BY started_at, run_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS rolling_3_run_avg FROM pipeline_runs ORDER BY pipeline_name, started_at, run_id;Why this works
A bounded ROWS frame implements a physical rolling window and naturally uses fewer available rows at the beginning of each partition.
Success check
Early partitions use available rows 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 | duration_seconds | rolling_3_run_avg |
|---|---|---|---|---|
| billing_rollup | 301 | 2026-06-03 08:00:00 | 120 | 120 |
| billing_rollup | 302 | 2026-06-03 09:00:00 | 110 | 115 |
| billing_rollup | 303 | 2026-06-03 10:00:00 | 90 | 106.67 |
| billing_rollup | 304 | 2026-06-03 11:00:00 | 90 | 96.67 |
| customer_sync | 201 | 2026-06-03 09:30:00 | 30 | 30 |
| customer_sync | 202 | 2026-06-03 10:30:00 | 35 | 32.5 |
| customer_sync | 203 | 2026-06-03 11:30:00 | 35 | 33.33 |
| customer_sync | 204 | 2026-06-03 12:30:00 | 25 | 31.67 |
| ingest_orders | 101 | 2026-06-03 09:00:00 | 60 | 60 |
| ingest_orders | 102 | 2026-06-03 10:00:00 | 45 | 52.5 |
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.