Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

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.

Solution

Method 1 - Window Function (RANK)

We use window functions to rank teams before and after the points change, then compute the difference.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

WITH before AS (
  SELECT team_id, name, points,
    RANK() OVER (ORDER BY points DESC, name ASC) AS rnk
  FROM TeamPoints
), after AS (
  SELECT t.team_id, t.name, t.points + c.points_change AS points,
    RANK() OVER (ORDER BY t.points + c.points_change DESC, t.name ASC) AS rnk
  FROM TeamPoints t
  JOIN PointsChange c ON t.team_id = c.team_id
)
SELECT b.team_id, b.name, b.rnk - a.rnk AS rank_diff
FROM before b
JOIN after a ON b.team_id = a.team_id;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

WITH before AS (
  SELECT team_id, name, points,
    RANK() OVER (ORDER BY points DESC, name ASC) AS rnk
  FROM TeamPoints
), after AS (
  SELECT t.team_id, t.name, t.points + c.points_change AS points,
    RANK() OVER (ORDER BY t.points + c.points_change DESC, t.name ASC) AS rnk
  FROM TeamPoints t
  JOIN PointsChange c ON t.team_id = c.team_id
)
SELECT b.team_id, b.name, b.rnk - a.rnk AS rank_diff
FROM before b
JOIN after a ON b.team_id = a.team_id;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

WITH before AS (
  SELECT team_id, name, points,
    RANK() OVER (ORDER BY points DESC, name ASC) AS rnk
  FROM TeamPoints
), after AS (
  SELECT t.team_id, t.name, t.points + c.points_change AS points,
    RANK() OVER (ORDER BY t.points + c.points_change DESC, t.name ASC) AS rnk
  FROM TeamPoints t
  JOIN PointsChange c ON t.team_id = c.team_id
)
SELECT b.team_id, b.name, b.rnk - a.rnk AS rank_diff
FROM before b
JOIN after a ON b.team_id = a.team_id;

Explanation

  • We rank teams before and after the points change using RANK().
  • We join the two rankings by team_id and compute the difference.
  • A positive rank_diff means the team dropped in rank, negative means it improved.

Complexity

  • ⏰ Time complexity: O(N log N) where N is the number of teams.
  • 🧺 Space complexity: O(N) for the window function.