Rank Throughput Within Each Pipeline
Rank runs by rows_loaded inside each pipeline_name partition.
- Window functions
- Sorting
Exercise brief
Understand the request
Data operations engineer Run performance is meaningful within each pipeline rather than across workloads of different sizes.
Run performance is meaningful within each pipeline rather than across workloads of different sizes. Rank runs by rows_loaded inside each pipeline_name partition.
Return
- Return pipeline_name, run_id, rows_loaded, and pipeline_rank.
- Order by pipeline_name, pipeline_rank, and run_id.
Constraints
- Partition the RANK window by pipeline_name.
- Preserve ties on rows_loaded.
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
A partition resets window state at each business group.
Hint 2
Put pipeline_name in PARTITION BY and rows_loaded DESC in the window ORDER BY.
Hint 3
Use a separate final ORDER BY for stable presentation.
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, RANK() OVER (PARTITION BY pipeline_name ORDER BY rows_loaded DESC) AS pipeline_rank FROM pipeline_runs ORDER BY pipeline_name, pipeline_rank, run_id;Why this works
PARTITION BY scopes the leaderboard to each pipeline while RANK retains all throughput peers. The rank state restarts at every pipeline boundary, and a separate final sort keeps peer presentation stable without changing the peer definition.
Success check
Ranks restart for each pipeline and tied top runs all receive rank 1.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | rows_loaded | pipeline_rank |
|---|---|---|---|
| billing_rollup | 301 | 1500 | 1 |
| billing_rollup | 302 | 1500 | 1 |
| billing_rollup | 303 | 1300 | 3 |
| billing_rollup | 304 | 1100 | 4 |
| customer_sync | 201 | 850 | 1 |
| customer_sync | 202 | 850 | 1 |
| customer_sync | 203 | 850 | 1 |
| customer_sync | 204 | 650 | 4 |
| ingest_orders | 102 | 1200 | 1 |
| ingest_orders | 103 | 1200 | 1 |
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.