Allocate Runs into Deterministic Quartiles with NTILE
Assign NTILE(4) by rows_loaded descending with run_id as the allocation tie-breaker.
- Window functions
- Sorting
Exercise brief
Understand the request
Capacity management analyst A fixed-size review program divides runs into four near-equal throughput buckets.
A fixed-size review program divides runs into four near-equal throughput buckets. Assign NTILE(4) by rows_loaded descending with run_id as the allocation tie-breaker.
Return
- Return run_id, rows_loaded, and throughput_quartile.
- Order by throughput_quartile and run_id.
Constraints
- Use NTILE(4).
- Make allocation deterministic even when rows_loaded ties cross a bucket boundary.
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
NTILE balances row counts; it does not promise to preserve peer groups.
Hint 2
A stable secondary key makes boundary allocation repeatable.
Hint 3
Use NTILE(4) over rows_loaded DESC, run_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT run_id, rows_loaded, NTILE(4) OVER (ORDER BY rows_loaded DESC, run_id) AS throughput_quartile FROM pipeline_runs ORDER BY throughput_quartile, run_id;Why this works
NTILE is a row-allocation function rather than a tie-inclusive percentile rule. A peer group can span buckets, so deterministic tie-breaking is essential.
Success check
All four buckets contain three runs and repeated execution is stable.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| run_id | rows_loaded | throughput_quartile |
|---|---|---|
| 301 | 1500 | 1 |
| 302 | 1500 | 1 |
| 303 | 1300 | 1 |
| 102 | 1200 | 2 |
| 103 | 1200 | 2 |
| 304 | 1100 | 2 |
| 101 | 1000 | 3 |
| 104 | 900 | 3 |
| 201 | 850 | 3 |
| 202 | 850 | 4 |
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.