lesson 46 Window functions · advanced

NTILE & balanced ranking buckets

Split ordered rows into ranking buckets.

the idea

NTILE(n) splits the ordered rows of a window into n buckets of as equal size as possible and returns each row’s bucket number. Use it for quartiles or deciles of a population, ordering by the measure and a tie-breaker.

Dataset: Sports

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Split teams into three buckets. Run this. NTILE(3) sorts teams by wins and labels each with tier 1, 2, or 3—roughly equal-sized groups for comparing standings.the pattern

   example.sql Run

   sql›

   SELECT
     team_id,
     wins,
     NTILE(3) OVER (ORDER BY wins DESC, team_id) AS tier
   FROM standings
   WHERE season = 2025 AND as_of_date = (
     SELECT last_regular_date
     FROM seasons
     WHERE season = 2025
   );

2. 2

   Preview qualified batting averages. Run this. Only players with at least 400 plate appearances appear—the pool you will quartile in the tasks.

   step-2.sql Run

   sql›

   SELECT
     s.player_id,
     SUM(s.plate_appearances) AS pa,
     ROUND(1.0 * SUM(s.hits) / SUM(s.at_bats), 3) AS batting_avg
   FROM player_game_stats s
   JOIN games g ON g.game_id = s.game_id
   WHERE g.game_type = 'regular'
   GROUP BY s.player_id
   HAVING SUM(s.plate_appearances) >= 400
   ORDER BY batting_avg DESC
   LIMIT 10;

common mistake

NTILE balances counts, not values: tied batting averages can land in different quartiles, so a deterministic tie-breaker matters.