Ranking Shift After Point Updates

Medium

A sports analytics platform tracks team rating points and refreshes world standings after each competition window. Table: TeamPoints team_id: unique integer identifier for each national team name: unique team name (country label) points: current score used for global ranking Table: PointsChange team_id: unique integer team identifier points_change: signed change applied to that team's points Ranking policy: Teams are ranked by points in descending order. If two teams have the same points, the team with lexicographically smaller name ranks higher. Task: Compute each team's rank before updates. Apply points_change to compute updated points. Compute each team's rank after updates. Return rank_diff = old_rank - new_rank for each team. Output requirements: Return columns: team_id, name, rank_diff Return one row per team appearing in TeamPoints Result can be returned in any order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

TeamPoints:
| team_id | name     | points |
|---------|----------|--------|
| 1       | atlas    | 2110   |
| 2       | bora     | 1780   |
| 3       | celta    | 1770   |
| 4       | delta    | 1400   |

PointsChange:
| team_id | points_change |
|---------|---------------|
| 1       | -25           |
| 2       | -10           |
| 3       | 5             |
| 4       | 450           |

Output

[
  {"team_id":1,"name":"atlas","rank_diff":0},
  {"team_id":2,"name":"bora","rank_diff":-2},
  {"team_id":3,"name":"celta","rank_diff":0},
  {"team_id":4,"name":"delta","rank_diff":2}
]

Explanation

Delta climbs from rank 4 to rank 2 after a large gain, while Bora drops from rank 2 to rank 4 after losing points.

Example 2

Input

TeamPoints:
| team_id | name   | points |
|---------|--------|--------|
| 101     | zara   | 95     |
| 102     | alex   | 90     |
| 103     | bella  | 90     |

PointsChange:
| team_id | points_change |
|---------|---------------|
| 101     | -5            |
| 102     | 0             |
| 103     | 0             |

Output

[
  {"team_id":101,"name":"zara","rank_diff":-2},
  {"team_id":102,"name":"alex","rank_diff":1},
  {"team_id":103,"name":"bella","rank_diff":1}
]

Explanation

All teams end at 90 points. Lexicographic order (alex < bella < zara) decides final ranking.

Example 3

Input

TeamPoints: []
PointsChange: []

Output

[]

Explanation

No teams means no ranking-difference rows.

Constraints

0 <= number of rows in TeamPoints <= 700
number of rows in PointsChange equals number of rows in TeamPoints
1 <= team_id <= 1000000000
team_id is unique in TeamPoints and in PointsChange
name is unique in TeamPoints
-100000 <= points <= 100000
-20000 <= points_change <= 20000
Every team_id in TeamPoints appears exactly once in PointsChange
Rank uses ordering: points DESC, name ASC
Return columns team_id, name, rank_diff
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns team_id, name, rank_diff

TeamPoints: | team_id | name | points | |---------|----------|--------| | 1 | atlas | 2110 | | 2 | bora | 1780 | | 3 | celta | 1770 | | 4 | delta | 1400 | PointsChange: | team_id | points_change | |---------|---------------| | 1 | -25 | | 2 | -10 | | 3 | 5 | | 4 | 450 |

TeamPoints: | team_id | name | points | |---------|--------|--------| | 101 | zara | 95 | | 102 | alex | 90 | | 103 | bella | 90 | PointsChange: | team_id | points_change | |---------|---------------| | 101 | -5 | | 102 | 0 | | 103 | 0 |