SQL
Rank Query Results Using SQL Window Functions
Understand how to assign ranks to rows within partitions using `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` window functions for leaderboards, top N queries, or identifying distinct positions.
CREATE TABLE scores (
player_name VARCHAR(50),
game_id INT,
score INT
);
INSERT INTO scores (player_name, game_id, score) VALUES
('Alice', 1, 100),
('Bob', 1, 95),
('Charlie', 1, 100),
('David', 1, 80),
('Alice', 2, 120),
('Bob', 2, 110),
('Charlie', 2, 120),
('Eve', 2, 90);
SELECT
player_name,
game_id,
score,
ROW_NUMBER() OVER (PARTITION BY game_id ORDER BY score DESC) AS row_num,
RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS dense_rank_num
FROM
scores
ORDER BY
game_id, score DESC;
How it works: This SQL snippet illustrates the use of ranking window functions (`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`) to assign a position to each row within groups (partitions). `PARTITION BY game_id` means ranking restarts for each game. `ORDER BY score DESC` defines the order within the partition. `ROW_NUMBER()` assigns a unique sequential number. `RANK()` assigns the same rank to tied rows and then skips subsequent numbers. `DENSE_RANK()` also assigns the same rank to tied rows but does not skip numbers, providing a continuous sequence. This is useful for leaderboards or "top N" queries.