Assign a Deterministic Global Run Sequence
Number runs from longest to shortest duration, breaking duration ties by run_id.
- Window functions
- Sorting
Exercise brief
Understand the request
Incident response lead A slow-run review queue requires exactly one stable sequence number for every run.
A slow-run review queue requires exactly one stable sequence number for every run. Number runs from longest to shortest duration, breaking duration ties by run_id.
Return
- Return run_id, duration_seconds, and duration_sequence.
- Order by duration_sequence.
Constraints
- Use ROW_NUMBER.
- Include run_id as the final window tie-breaker.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
run_idINTEGERduration_secondsINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
ROW_NUMBER always assigns unique values, so its ordering must be total.
Hint 2
Sort by duration_seconds DESC and then by the stable key.
Hint 3
Use the same sequence alias in the final ORDER BY.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT run_id, duration_seconds, ROW_NUMBER() OVER (ORDER BY duration_seconds DESC, run_id) AS duration_sequence FROM pipeline_runs ORDER BY duration_sequence;Why this works
ROW_NUMBER is appropriate for exact allocation, but without a unique final ordering key tied rows can change positions between executions.
Success check
Every row gets one repeatable sequence value even when durations tie.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| run_id | duration_seconds | duration_sequence |
|---|---|---|
| 301 | 120 | 1 |
| 302 | 110 | 2 |
| 303 | 90 | 3 |
| 304 | 90 | 4 |
| 104 | 80 | 5 |
| 101 | 60 | 6 |
| 102 | 45 | 7 |
| 103 | 45 | 8 |
| 202 | 35 | 9 |
| 203 | 35 | 10 |
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.