Drill 7 of 12 in Gaming questions
0

Interview Question Bank · DENSE_RANK · PARTITION BY · NULL sorting

7. Season podium with ties

MediumGamingQuestion #151

Concept: DENSE_RANK · PARTITION BY · NULL sortingshow

DENSE_RANK() OVER (PARTITION BY group ORDER BY score DESC) numbers rows within each group so tied scores share a place and the next place follows without a gap (1, 1, 2). RANK leaves a gap after ties (1, 1, 3) and ROW_NUMBER splits ties arbitrarily. Watch out for NULL scores: Postgres sorts NULL first in descending order, so filter them out or add NULLS LAST.

Example (different data):

WITH r AS (
  SELECT league, club, goals,
    DENSE_RANK() OVER (PARTITION BY league ORDER BY goals DESC) AS place
  FROM club_goals
  WHERE goals IS NOT NULL
)
SELECT * FROM r WHERE place <= 2;

Your task

Skyrift Arena awards a podium badge to everyone who finishes in the top 3 places of a ranked season. Tied players share a place, and the badge list for all seasons is being rebuilt.

Rank players within each season by points, highest first, so tied players share a place and places have no gaps (1, 1, 2). Players with NULL points never played and aren't ranked. Return season, place, username and points for places 1 to 3. Sort by season, place, username.

Starting Postgres in your browser…

Checking your access…