lesson 45 Window functions · advanced

Top N per group & ties

Find the leading records within each group and handle ties.

the idea

Top-N per group = rank within each group, then filter. DENSE_RANK keeps every player at the top N distinct values, ties included; ROW_NUMBER keeps exactly N rows per group and needs a tie-breaker to decide who is cut.

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

   Pick one hits leader per team. Run this. After summing hits by player, ROW_NUMBER() = 1 keeps a single top hitter per team, breaking ties with the smaller player_id.

   step-1.sql Run

   sql›

   SELECT team_id, player_id, hits
   FROM (
     SELECT
       s.team_id,
       s.player_id,
       SUM(s.hits) AS hits,
       ROW_NUMBER() OVER (PARTITION BY s.team_id ORDER BY SUM(s.hits) DESC, s.player_id) AS rn
     FROM player_game_stats s
     JOIN games g ON g.game_id = s.game_id
     WHERE g.game_type = 'regular'
     GROUP BY s.team_id, s.player_id
   )
   WHERE rn = 1;

2. 2

   Find tied home-run totals. Run this. Groups with more than one player show why DENSE_RANK keeps every tied name when you ask for “top two totals.”

   step-2.sql Run

   sql›

   SELECT team_id, home_runs, COUNT(*) AS players
   FROM (
     SELECT s.team_id, 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.team_id, s.player_id
   )
   WHERE home_runs > 15
   GROUP BY team_id, home_runs
   HAVING COUNT(*) > 1;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT team_id, player_id, hits
   FROM (
     SELECT
       s.team_id,
       s.player_id,
       SUM(s.hits) AS hits,
       ROW_NUMBER() OVER (PARTITION BY s.team_id ORDER BY SUM(s.hits) DESC, s.player_id) AS rn
     FROM player_game_stats s
     JOIN games g ON g.game_id = s.game_id
     WHERE g.game_type = 'regular'
     GROUP BY s.team_id, s.player_id
   )
   WHERE rn = 1;

common mistake

LIMIT applies to the whole result, not to each team.