The Change in Global Rankings - Problem

You are given two tables: TeamPoints containing national teams and their current points, and PointsChange containing point changes for each team.

The global ranking is determined by:

  • Teams sorted by points in descending order
  • Ties broken by team name in lexicographical order

Your task is to:

  1. Update each team's points using the points_change values
  2. Calculate the ranking before and after the update
  3. Return the change in ranking for each team

Return the result showing team_id, name, and ranking_change (negative means rank improved, positive means rank worsened).

Table Schema

TeamPoints
Column Name Type Description
team_id PK int Unique team identifier
name varchar Country name the team represents
points int Current points in global rankings
Primary Key: team_id
PointsChange
Column Name Type Description
team_id PK int Unique team identifier
points_change int Change in points (positive=increase, negative=decrease, 0=no change)
Primary Key: team_id

Input & Output

Example 1 — Basic Ranking Changes
Input Tables:
TeamPoints
team_id name points
1 Brazil 80
2 Germany 60
3 Spain 40
PointsChange
team_id points_change
1 -20
2 25
3 -10
Output:
team_id name ranking_change
1 Brazil 1
2 Germany -1
3 Spain 0
💡 Note:

Initially: Brazil (80pts, rank 1), Germany (60pts, rank 2), Spain (40pts, rank 3). After changes: Germany (85pts, rank 1), Brazil (60pts, rank 2), Spain (30pts, rank 3). Germany improved by 1 rank (-1), Brazil dropped by 1 rank (+1), Spain stayed the same (0).
Example 2 — Tie Breaking by Name
Input Tables:
TeamPoints
team_id name points
1 Italy 50
2 France 50
PointsChange
team_id points_change
1 0
2 0
Output:
team_id name ranking_change
2 France 0
1 Italy 0
💡 Note:

Both teams have 50 points before and after changes. Since points are equal, rankings are determined by name lexicographically: France (rank 1), Italy (rank 2). No point changes means no ranking changes.

Constraints

  • 1 ≤ team_id ≤ 1000
  • 1 ≤ name.length ≤ 100
  • 0 ≤ points ≤ 1000
  • -1000 ≤ points_change ≤ 1000
  • Each team in TeamPoints appears exactly once in PointsChange

Visualization

Tap to expand
Global Rankings: Before and After Point ChangesTeamPoints (Original)namepointsrankBrazil801Germany602Spain403UPDATEPOINTSAfter Point ChangesnamepointsrankGermany851Brazil602Spain303Ranking ChangesteamchangeBrazil+1Germany-1Spain0Key: Negative = Rank Improved, Positive = Rank Worsened
Understanding the Visualization
1
Original Rankings
Teams ranked by points DESC, name ASC
2
Apply Changes
Update points using PointsChange table
3
New Rankings
Recalculate rankings and find differences
Key Takeaway
🎯 Key Insight: Use window functions with proper ORDER BY clause (points DESC, name ASC) to handle ranking with tie-breaking

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen