Table: TeamPoints
+-------------+---------+ | Column Name | Type | +-------------+---------+ | team_id | int | | name | varchar | | points | int | +-------------+---------+ team_id contains unique values. Each row of this table contains the ID of a national team, the name of the country it represents, and the points it has in the global rankings. No two teams will represent the same country.
Table: PointsChange
+---------------+------+ | Column Name | Type | +---------------+------+ | team_id | int | | points_change | int | +---------------+------+ team_id contains unique values. Each row of this table contains the ID of a national team and the change in its points in the global rankings. points_change can be: - 0: indicates no change in points. - positive: indicates an increase in points. - negative: indicates a decrease in points. Each team_id that appears in TeamPoints will also appear in this table.
The global ranking of a national team is its rank after sorting all the teams by their points in descending order. If two teams have the same points, we break the tie by sorting them by their name in lexicographical order.
The points of each national team should be updated based on its corresponding points_change value.
Write a solution to calculate the change in the global rankings after updating each team's points.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: TeamPoints table: +---------+-------------+--------+ | team_id | name | points | +---------+-------------+--------+ | 3 | Algeria | 1431 | | 1 | Senegal | 2132 | | 2 | New Zealand | 1402 | | 4 | Croatia | 1817 | +---------+-------------+--------+ PointsChange table: +---------+---------------+ | team_id | points_change | +---------+---------------+ | 3 | 399 | | 2 | 0 | | 4 | 13 | | 1 | -22 | +---------+---------------+ Output: +---------+-------------+-----------+ | team_id | name | rank_diff | +---------+-------------+-----------+ | 1 | Senegal | 0 | | 4 | Croatia | -1 | | 3 | Algeria | 1 | | 2 | New Zealand | 0 | +---------+-------------+-----------+ Explanation: The global rankings were as follows: +---------+-------------+--------+------+ | team_id | name | points | rank | +---------+-------------+--------+------+ | 1 | Senegal | 2132 | 1 | | 4 | Croatia | 1817 | 2 | | 3 | Algeria | 1431 | 3 | | 2 | New Zealand | 1402 | 4 | +---------+-------------+--------+------+ After updating the points of each team, the rankings became the following: +---------+-------------+--------+------+ | team_id | name | points | rank | +---------+-------------+--------+------+ | 1 | Senegal | 2110 | 1 | | 3 | Algeria | 1830 | 2 | | 4 | Croatia | 1830 | 3 | | 2 | New Zealand | 1402 | 4 | +---------+-------------+--------+------+ Since after updating the points Algeria and Croatia have the same points, they are ranked according to their lexicographic order. Senegal lost 22 points but their rank did not change. Croatia gained 13 points but their rank decreased by one. Algeria gained 399 points and their rank increased by one. New Zealand did not gain or lose points and their rank did not change.
Problem Overview: You’re given a table of teams with their current points and another table describing point changes. After applying the changes, compute how each team’s global ranking shifts. The task is to calculate the original rank, the new rank after the update, and return the difference between them.
Approach 1: Window Functions with Rank Comparison (O(n log n) time, O(n) space)
The clean solution calculates rankings before and after applying the point changes, then compares them. Start by joining the teams table with the change table so each team has both its current points and its updated points (points + points_change). Next, compute the original ranking using a window function like DENSE_RANK() OVER (ORDER BY points DESC). Compute the updated ranking using the same function but order by the new point total.
Once both rankings are available, subtract the new rank from the old rank to measure how the team moved on the leaderboard. Positive values mean the team climbed the rankings; negative values mean it dropped. Window functions handle ranking efficiently because the database performs the ordering and ranking in a single pass after sorting.
This approach is standard for database problems involving leaderboard calculations. The key insight is that rankings are simply ordered projections of the same dataset before and after a transformation. SQL window functions make these comparisons straightforward without writing complex nested queries.
An implementation typically uses a Common Table Expression (CTE) or subquery to compute both ranks, then returns the ranking difference. MySQL supports DENSE_RANK(), making this solution concise and efficient.
Recommended for interviews: The window‑function approach is the expected solution. Interviewers want to see that you recognize leaderboard problems as ranking problems and use SQL analytic functions such as DENSE_RANK or RANK. A manual ranking simulation with correlated subqueries works but is harder to read and slower. Using window functions shows strong familiarity with modern SQL analytics and ranking queries common in database interview questions.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Window Functions (DENSE_RANK) | O(n log n) | O(n) | Best general solution. Clean ranking comparison using SQL analytic functions. |
| Correlated Subquery Ranking | O(n^2) | O(1) | When window functions are unavailable. Computes rank by counting higher scores. |
LeetCode Medium 2175 "Change in Global Rankings" Interview SQL Question with Detailed Explanation • Everyday Data Science • 2,132 views views
Watch 1 more video solutions →Practice The Change in Global Rankings with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor