SQL
SQL Window Function for Ranking and Nth Highest Value
Utilize SQL window functions like `RANK()` or `DENSE_RANK()` to assign ranks to rows within partitions, finding Nth highest values, or top performers.
SELECT
user_id,
score,
ranking
FROM (
SELECT
user_id,
score,
RANK() OVER (ORDER BY score DESC) AS ranking
FROM game_scores
WHERE game_id = 123
) AS RankedScores
WHERE ranking <= 3;
How it works: This query uses the `RANK()` window function to assign a rank to each user's score within a specific game (`game_id = 123`). The `ORDER BY score DESC` within `OVER()` means higher scores get a lower rank (1st, 2nd, etc.). The outer query then filters these ranked results to retrieve only the top 3 users (`ranking <= 3`). Window functions are powerful for complex analytical queries, leaderboards, or finding specific Nth elements in a dataset without self-joins or subqueries. Other related functions include `DENSE_RANK()` (no gaps in rank) and `ROW_NUMBER()` (unique number for each row).