Compare PERCENT_RANK with CUME_DIST at Ties
Calculate both distribution measures over throughput and inspect the tied 850-row runs.
- Window functions
- Subqueries
- Numeric functions
- Type conversion
- Filtering
Exercise brief
Understand the request
Data platform interviewer A percentile review must distinguish relative starting position from the cumulative share through an entire peer group.
A percentile review must distinguish relative starting position from the cumulative share through an entire peer group. Calculate both distribution measures over throughput and inspect the tied 850-row runs.
Return
- Return run_id, rows_loaded, percent_rank_value, and cumulative_distribution.
- Round both measures to three decimals and order by run_id.
Constraints
- Use the same rows_loaded DESC window order for both functions.
- Filter the computed window results in an outer query.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
run_idINTEGERrows_loadedINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
PERCENT_RANK uses the first rank of a peer group; CUME_DIST reaches its end.
Hint 2
Compute both functions before limiting the rows under inspection.
Hint 3
Put the distribution query in a CTE, then filter rows_loaded = 850.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH distributed AS (SELECT run_id, rows_loaded, ROUND(CAST(PERCENT_RANK() OVER (ORDER BY rows_loaded DESC) AS DECIMAL), 3) AS percent_rank_value, ROUND(CAST(CUME_DIST() OVER (ORDER BY rows_loaded DESC) AS DECIMAL), 3) AS cumulative_distribution FROM pipeline_runs) SELECT run_id, rows_loaded, percent_rank_value, cumulative_distribution FROM distributed WHERE rows_loaded = 850 ORDER BY run_id;Why this works
The two functions answer different percentile questions. PERCENT_RANK locates the peer group by its rank, while CUME_DIST counts every row through that group.
Success check
All three peers share both values, and CUME_DIST includes the full peer group.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| run_id | rows_loaded | percent_rank_value | cumulative_distribution |
|---|---|---|---|
| 201 | 850 | 0.727 | 0.917 |
| 202 | 850 | 0.727 | 0.917 |
| 203 | 850 | 0.727 | 0.917 |
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.