Keep Every Top-Throughput Tie Per Pipeline
Rank within each pipeline and keep pipeline_rank 1.
- Window functions
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Pipeline ownership lead Recognition reporting must include every run tied for best throughput in its pipeline.
Recognition reporting must include every run tied for best throughput in its pipeline. Rank within each pipeline and keep pipeline_rank 1.
Return
- Return pipeline_name, run_id, and rows_loaded.
- Order by pipeline_name and run_id.
Constraints
- Use RANK, not ROW_NUMBER.
- Filter the window result outside the windowed SELECT.
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
This is a tie-inclusive top-1-per-group problem.
Hint 2
RANK only by the performance measure inside each partition.
Hint 3
Filter pipeline_rank = 1 in the outer query.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH ranked AS (SELECT run_id, pipeline_name, rows_loaded, RANK() OVER (PARTITION BY pipeline_name ORDER BY rows_loaded DESC) AS pipeline_rank FROM pipeline_runs) SELECT pipeline_name, run_id, rows_loaded FROM ranked WHERE pipeline_rank = 1 ORDER BY pipeline_name, run_id;Why this works
RANK expresses a tie-inclusive cutoff. Adding run_id to the ranking order would silently convert peer ties into different ranks.
Success check
The three-way customer_sync tie and both two-way ties are retained.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | rows_loaded |
|---|---|---|
| billing_rollup | 301 | 1500 |
| billing_rollup | 302 | 1500 |
| customer_sync | 201 | 850 |
| customer_sync | 202 | 850 |
| customer_sync | 203 | 850 |
| ingest_orders | 102 | 1200 |
| ingest_orders | 103 | 1200 |
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.