Broadcast the Second Ordered Run with NTH_VALUE
Use NTH_VALUE(run_id, 2) over the full pipeline partition.
- Window functions
- Sorting
Exercise brief
Understand the request
Warehouse analytics engineer Each run needs the identifier of the second row in a deterministic per-pipeline throughput ordering.
Each run needs the identifier of the second row in a deterministic per-pipeline throughput ordering. Use NTH_VALUE(run_id, 2) over the full pipeline partition.
Return
- Return pipeline_name, run_id, rows_loaded, and second_run_id.
- Order by pipeline_name and run_id.
Constraints
- Order by rows_loaded DESC and run_id.
- Use a full-partition ROWS frame.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
pipeline_nameTEXTrun_idINTEGERrows_loadedINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
NTH_VALUE counts physical rows in the ordered frame, not distinct values.
Hint 2
A deterministic peer order defines which row is second.
Hint 3
Extend the frame through UNBOUNDED FOLLOWING so early rows can see position 2.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT pipeline_name, run_id, rows_loaded, NTH_VALUE(run_id, 2) OVER (PARTITION BY pipeline_name ORDER BY rows_loaded DESC, run_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_run_id FROM pipeline_runs ORDER BY pipeline_name, run_id;Why this works
NTH_VALUE is positional. The stable ordering defines the selected row and the full frame avoids leading NULLs caused by an incomplete default frame.
Success check
Every row in a pipeline receives one stable second_run_id.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | rows_loaded | second_run_id |
|---|---|---|---|
| billing_rollup | 301 | 1500 | 302 |
| billing_rollup | 302 | 1500 | 302 |
| billing_rollup | 303 | 1300 | 302 |
| billing_rollup | 304 | 1100 | 302 |
| customer_sync | 201 | 850 | 202 |
| customer_sync | 202 | 850 | 202 |
| customer_sync | 203 | 850 | 202 |
| customer_sync | 204 | 650 | 202 |
| ingest_orders | 101 | 1000 | 103 |
| ingest_orders | 102 | 1200 | 103 |
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.