Measure Relative Runtime Rank per Pipeline
Calculate duration_percent_rank per pipeline and round it to three decimals.
- Window functions
- Numeric functions
- Type conversion
- Sorting
Exercise brief
Understand the request
Performance analytics engineer A normalized runtime indicator must compare each run with others from the same pipeline while preserving duration peers.
A normalized runtime indicator must compare each run with others from the same pipeline while preserving duration peers. Calculate duration_percent_rank per pipeline and round it to three decimals.
Return
- Return pipeline_name, run_id, duration_seconds, and duration_percent_rank.
- Order by pipeline_name, duration_seconds, and run_id.
Constraints
- Use PERCENT_RANK with duration_seconds only in the window order.
- Do not break duration peers with run_id inside the window.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_runs
pipeline_nameTEXTrun_idINTEGERduration_secondsINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
PERCENT_RANK is based on RANK, so peers should stay peers.
Hint 2
Partition by pipeline_name and order only by duration_seconds.
Hint 3
Round the function result, not the source duration.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT pipeline_name, run_id, duration_seconds, ROUND(CAST(PERCENT_RANK() OVER (PARTITION BY pipeline_name ORDER BY duration_seconds) AS DECIMAL), 3) AS duration_percent_rank FROM pipeline_runs ORDER BY pipeline_name, duration_seconds, run_id;Why this works
PERCENT_RANK computes (rank − 1)/(partition rows − 1). Because it is rank-based, tied durations share a result.
Success check
Equal durations share the same relative rank and each partition spans 0 to 1.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| pipeline_name | run_id | duration_seconds | duration_percent_rank |
|---|---|---|---|
| billing_rollup | 303 | 90 | 0 |
| billing_rollup | 304 | 90 | 0 |
| billing_rollup | 302 | 110 | 0.667 |
| billing_rollup | 301 | 120 | 1 |
| customer_sync | 204 | 25 | 0 |
| customer_sync | 201 | 30 | 0.333 |
| customer_sync | 202 | 35 | 0.667 |
| customer_sync | 203 | 35 | 0.667 |
| ingest_orders | 102 | 45 | 0 |
| ingest_orders | 103 | 45 | 0 |
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.