lesson 36 Window functions · advanced

RANK & DENSE_RANK

Rank rows and decide how ties should behave.

the idea

RANK and DENSE_RANK give equal values the same rank. After a tie, RANK skips positions (1, 2, 2, 4) and DENSE_RANK does not (1, 2, 2, 3). Aggregate first to the grain you want to rank, then apply the window.

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

   Add up home runs per player. Run this first. Each row is one player’s total home runs for the 2025 regular season—you rank totals like this, not single-game lines.

   step-1.sql Run

   sql›

   SELECT s.player_id, SUM(s.home_runs) AS home_runs
   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
   ORDER BY home_runs DESC
   LIMIT 15;

2. 2

   Rank teams by win total. Run this. RANK() gives the same number to teams tied on wins and skips the next slot after a tie (1, 2, 2, 4).the pattern

   example.sql Run

   sql›

   SELECT team_id, wins, RANK() OVER (ORDER BY wins DESC) AS wins_rank
   FROM standings
   WHERE season = 2024 AND as_of_date = (
     SELECT last_regular_date
     FROM seasons
     WHERE season = 2024
   );

common mistake

Ranking batting lines instead of player totals ranks single games, not seasons.