Rank Pipeline Runs While Preserving Ties
Rank every pipeline run by rows_loaded descending with RANK, then present peers by run_id.
- Window functions
- Sorting
Exercise brief
Understand the request
Platform reliability analyst A throughput leaderboard must give runs with the same rows_loaded the same position.
A throughput leaderboard must give runs with the same rows_loaded the same position. Rank every pipeline run by rows_loaded descending with RANK, then present peers by run_id.
Return
- Return run_id, pipeline_name, rows_loaded, and throughput_rank.
- Order by throughput_rank and run_id.
Constraints
- Use RANK() OVER (ORDER BY rows_loaded DESC).
- Do not add run_id inside the ranking window because that would break peer ties.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
run_idINTEGERpipeline_nameTEXTrows_loadedINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
RANK preserves peer groups and leaves gaps after ties.
Hint 2
The window order defines ranks; the final ORDER BY defines display order.
Hint 3
Rank only by rows_loaded DESC, then sort the result by throughput_rank and run_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT run_id, pipeline_name, rows_loaded, RANK() OVER (ORDER BY rows_loaded DESC) AS throughput_rank FROM pipeline_runs ORDER BY throughput_rank, run_id;Why this works
RANK assigns one value to every peer and advances by the peer-group size. Keeping the stable key outside the window preserves ties while making output deterministic.
Success check
Equal throughput values share a rank and the next rank contains the required gap.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| run_id | pipeline_name | rows_loaded | throughput_rank |
|---|---|---|---|
| 301 | billing_rollup | 1500 | 1 |
| 302 | billing_rollup | 1500 | 1 |
| 303 | billing_rollup | 1300 | 3 |
| 102 | ingest_orders | 1200 | 4 |
| 103 | ingest_orders | 1200 | 4 |
| 304 | billing_rollup | 1100 | 6 |
| 101 | ingest_orders | 1000 | 7 |
| 104 | ingest_orders | 900 | 8 |
| 201 | customer_sync | 850 | 9 |
| 202 | customer_sync | 850 | 9 |
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.