Return the Third Distinct Throughput Level
Use DENSE_RANK in a CTE and filter the third throughput level.
- CTEs
- Window functions
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Capacity planning analyst Capacity review needs every run at the third-highest distinct throughput, not merely the third physical row.
Capacity review needs every run at the third-highest distinct throughput, not merely the third physical row. Use DENSE_RANK in a CTE and filter the third throughput level.
Return
- Return run_id, pipeline_name, and rows_loaded.
- Order by run_id.
Constraints
- Rank rows_loaded descending with DENSE_RANK.
- Filter the window result in an outer query.
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
Nth distinct value means counting peer groups, not rows.
Hint 2
DENSE_RANK produces consecutive values after ties.
Hint 3
Compute throughput_level in a CTE, then keep level 3.
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, DENSE_RANK() OVER (ORDER BY rows_loaded DESC) AS throughput_level FROM pipeline_runs) SELECT run_id, pipeline_name, rows_loaded FROM ranked WHERE throughput_level = 3 ORDER BY run_id;Why this works
DENSE_RANK maps each distinct ordered value to a consecutive level, so filtering level 3 preserves every tied row at that value.
Success check
All runs tied at the third distinct rows_loaded value are returned.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| run_id | pipeline_name | rows_loaded |
|---|---|---|
| 102 | ingest_orders | 1200 |
| 103 | ingest_orders | 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.