Top Three Run Totals in Every League Team — SQL Hard Problem

Problem statement

The inter-college cricket league awards a medal to each player whose season run total is among the three highest distinct totals in their team. Equal totals share a medal, so a team can have more than three medallists: totals of 410, 410, 385, 300, 120 give medals to the four players with 410, 385 and 300. A player with NULL season_runs has not batted this season and never gets a medal, and a team with fewer than three distinct totals gives a medal to every player who batted.

Return every medallist with columns team (the team's name), player and runs. Teams with no players do not appear. Return the rows in any order.

Tables

Table: Team

Primary key: team_id.

One row per team.

Table: Player

Primary key: player_id.

One row per player; team_id always names a row of Team. season_runs is NULL for a player who has not batted.

Examples

Example 1

Team

Player

Output

How to solve Top Three Run Totals in Every League Team

The ranking must restart in each team, and equal totals must share a place without using up the next one: 410, 410, 385, 300 are places 1, 1, 2, 3, so all four players win medals. That is DENSE_RANK() OVER (PARTITION BY team_id ORDER BY season_runs DESC). RANK() would give 1, 1, 3, 4 and drop the 300; ROW_NUMBER() would drop a tied player outright.

Window functions are evaluated after WHERE, so the rank cannot be filtered in the query that computes it. Rank inside a derived table (or CTE), then keep medal_rank <= 3 outside and join Team for the name.

The NULL rule is the subtle part. In a descending order NULLs sort last, so in a team with only two distinct totals a NULL would get dense rank 3 and a medal. Filtering season_runs IS NOT NULL before ranking removes them. The correlated version has the same trap from the other side: for a NULL total, the count of higher totals is 0, which is "in the top three" — hence the same filter.

That correlated version reads naturally: a player medals when fewer than three distinct totals in their team are strictly higher. The self-join version counts those higher totals with LEFT JOIN … GROUP BY … HAVING. Both are quadratic within each team; the window plan is one sort by team and total.

Reference solution (MySQL)

SELECT t.team_name AS team, r.player_name AS player, r.season_runs AS runs
FROM (
  SELECT player_name, team_id, season_runs,
         DENSE_RANK() OVER (PARTITION BY team_id ORDER BY season_runs DESC) AS medal_rank
  FROM Player
  WHERE season_runs IS NOT NULL
) r
JOIN Team t ON t.team_id = r.team_id
WHERE r.medal_rank <= 3

Another way

SELECT t.team_name AS team, p.player_name AS player, p.season_runs AS runs
FROM Player p
JOIN Team t ON t.team_id = p.team_id
WHERE p.season_runs IS NOT NULL
  AND (SELECT COUNT(DISTINCT q.season_runs) FROM Player q
       WHERE q.team_id = p.team_id AND q.season_runs > p.season_runs) < 3

Another way

SELECT t.team_name AS team, p.player_name AS player, p.season_runs AS runs
FROM Player p
JOIN Team t ON t.team_id = p.team_id
LEFT JOIN Player q ON q.team_id = p.team_id AND q.season_runs > p.season_runs
WHERE p.season_runs IS NOT NULL
GROUP BY p.player_id, t.team_name, p.player_name, p.season_runs
HAVING COUNT(DISTINCT q.season_runs) < 3