The Change in Global Rankings - Practice Coding Problems

The Change in Global Rankings - Problem

Database Medium

Imagine you're working for an international sports federation tasked with tracking global team rankings! 🏆

You have two tables:

TeamPoints: Contains each team's current points and country name
PointsChange: Contains point changes for each team (positive = increase, negative = decrease, 0 = no change)

Teams are ranked by their points in descending order. If two teams have the same points, they're ordered alphabetically by country name.

Your mission: Calculate how much each team's global ranking position changed after applying the point updates!

For example, if Team A was ranked #3 before the update and #1 after, their ranking change is 3 - 1 = 2 (positive means they moved up).

Return a table showing each team's name and their ranking change, where:

Positive values = moved up in rankings
Negative values = moved down in rankings
Zero = same ranking position

Input & Output

example_1.sql — Basic Ranking Changes

$ Input: TeamPoints: | team_id | name | points | |---------|-----------|--------| | 1 | Brazil | 10 | | 2 | Argentina | 8 | | 3 | France | 12 | PointsChange: | team_id | points_change | |---------|---------------| | 1 | 2 | | 2 | -1 | | 3 | 0 |

› Output: | name | ranking_change | |-----------|----------------| | Argentina | -1 | | Brazil | 1 | | France | 0 |

💡 Note: Initially: France(12) #1, Brazil(10) #2, Argentina(8) #3. After changes: Brazil(12) #1, France(12) #2, Argentina(7) #3. France stays #2 due to alphabetical tiebreaker with Brazil. Brazil moves up 1 position (2-1=1), Argentina moves down 0 positions (3-3=0), France stays same (1-2=-1... wait, let me recalculate: France goes from #1 to #2, so 1-2=-1).

example_2.sql — Tie Breaking Scenario

$ Input: TeamPoints: | team_id | name | points | |---------|---------|--------| | 1 | Croatia | 15 | | 2 | Belgium | 15 | | 3 | Denmark | 12 | PointsChange: | team_id | points_change | |---------|---------------| | 1 | -3 | | 2 | 0 | | 3 | 3 |

› Output: | name | ranking_change | |---------|----------------| | Belgium | 0 | | Croatia | -1 | | Denmark | 2 |

💡 Note: Initially (tied at 15, alphabetical): Belgium #1, Croatia #2, Denmark #3. After changes: Denmark(15) #1, Belgium(15) #2, Croatia(12) #3. Belgium moves from #1 to #2 (1-2=-1), Croatia from #2 to #3 (2-3=-1), Denmark from #3 to #1 (3-1=2).

example_3.sql — All Same Points Edge Case

$ Input: TeamPoints: | team_id | name | points | |---------|-----------|--------| | 1 | Australia | 10 | | 2 | Canada | 10 | | 3 | Japan | 10 | PointsChange: | team_id | points_change | |---------|---------------| | 1 | 0 | | 2 | 0 | | 3 | 0 |

› Output: | name | ranking_change | |-----------|----------------| | Australia | 0 | | Canada | 0 | | Japan | 0 |

💡 Note: All teams have same points before and after, so rankings remain: Australia #1, Canada #2, Japan #3 (alphabetical order). No ranking changes occur.

Visualization

Tap to expand

Understanding the Visualization

Initial Rankings

Teams are ranked by points (highest first), with ties broken alphabetically

Apply Changes

Each team receives positive, negative, or zero point changes

New Rankings

Recalculate rankings with updated points using same rules

Calculate Shifts

Find difference: old_rank - new_rank (positive = moved up)

Key Takeaway

🎯 Key Insight: Use window functions to calculate both rankings simultaneously, then subtract to find position changes efficiently in a single query.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single sort operation for window function ranking

⚡ Linearithmic

Space Complexity

O(n)

Space for result set and window function processing

⚡ Linearithmic Space

Constraints

1 ≤ number of teams ≤ 10⁴
Team names contain only letters and are unique
-10³ ≤ points_change ≤ 10³
Each team_id in TeamPoints appears exactly once in PointsChange
1 ≤ original points ≤ 10⁶

Asked in

G Google 25 f Meta 18 a Amazon 22 ⊞ Microsoft 15

The optimal solution uses SQL window functions to calculate both old and new rankings in a single query. By using RANK() OVER clauses with proper ordering (points DESC, name ASC), we efficiently compute rankings and find their differences. This approach requires only O(n log n) time complexity and avoids multiple table scans, making it the most efficient solution for large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Single Query with Window Functions (Optimal)	O(n log n)	O(n)	Calculate both rankings in one query using window functions
Brute Force (Multiple CTEs)	O(n log n)	O(n)	Calculate old rankings, new rankings, then join to find differences

Single Query with Window Functions (Optimal) — Algorithm Steps

Join TeamPoints with PointsChange to get all data in one place
Use RANK() OVER to calculate old rankings based on original points
Calculate new points by adding points_change to original points
Use RANK() OVER to calculate new rankings based on updated points
Subtract new rank from old rank to get ranking change
Return results sorted by team name

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Combine TeamPoints and PointsChange data

Calculate Old Ranks

Use RANK() OVER on original points

Calculate New Points

Add points_change to original points

Calculate New Ranks

Use RANK() OVER on updated points

Find Difference

Subtract new rank from old rank

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int team_id;
    char name[100];
    int old_points;
    int new_points;
} TeamData;

typedef struct {
    char name[100];
    int ranking_change;
} Result;

int compare_old_points(const void *a, const void *b) {
    TeamData *ta = (TeamData *)a;
    TeamData *tb = (TeamData *)b;
    
    if (ta->old_points != tb->old_points) {
        return tb->old_points - ta->old_points;
    }
    return strcmp(ta->name, tb->name);
}

int compare_new_points(const void *a, const void *b) {
    TeamData *ta = (TeamData *)a;
    TeamData *tb = (TeamData *)b;
    
    if (ta->new_points != tb->new_points) {
        return tb->new_points - ta->new_points;
    }
    return strcmp(ta->name, tb->name);
}

int compare_results(const void *a, const void *b) {
    Result *ra = (Result *)a;
    Result *rb = (Result *)b;
    return strcmp(ra->name, rb->name);
}

Result* optimalSolution(int** teamPoints, int teamPointsSize, 
                       int** pointsChange, int pointsChangeSize, 
                       int* returnSize) {
    TeamData* teams = (TeamData*)malloc(teamPointsSize * sizeof(TeamData));
    TeamData* oldSorted = (TeamData*)malloc(teamPointsSize * sizeof(TeamData));
    TeamData* newSorted = (TeamData*)malloc(teamPointsSize * sizeof(TeamData));
    Result* results = (Result*)malloc(teamPointsSize * sizeof(Result));
    
    // Create changes map (simplified for small datasets)
    int changes[1000] = {0}; // Assuming team IDs < 1000
    for (int i = 0; i < pointsChangeSize; i++) {
        changes[pointsChange[i][0]] = pointsChange[i][1];
    }
    
    // Prepare team data
    for (int i = 0; i < teamPointsSize; i++) {
        teams[i].team_id = teamPoints[i][0];
        // Note: This is simplified - in real implementation, you'd need to handle string conversion
        sprintf(teams[i].name, "Team%d", teamPoints[i][0]);
        teams[i].old_points = teamPoints[i][2];
        teams[i].new_points = teamPoints[i][2] + changes[teamPoints[i][0]];
    }
    
    // Calculate old rankings
    memcpy(oldSorted, teams, teamPointsSize * sizeof(TeamData));
    qsort(oldSorted, teamPointsSize, sizeof(TeamData), compare_old_points);
    
    int oldRanks[1000] = {0};
    for (int i = 0; i < teamPointsSize; i++) {
        oldRanks[oldSorted[i].team_id] = i + 1;
    }
    
    // Calculate new rankings
    memcpy(newSorted, teams, teamPointsSize * sizeof(TeamData));
    qsort(newSorted, teamPointsSize, sizeof(TeamData), compare_new_points);
    
    int newRanks[1000] = {0};
    for (int i = 0; i < teamPointsSize; i++) {
        newRanks[newSorted[i].team_id] = i + 1;
    }
    
    // Calculate results
    for (int i = 0; i < teamPointsSize; i++) {
        strcpy(results[i].name, teams[i].name);
        results[i].ranking_change = oldRanks[teams[i].team_id] - newRanks[teams[i].team_id];
    }
    
    qsort(results, teamPointsSize, sizeof(Result), compare_results);
    
    *returnSize = teamPointsSize;
    free(teams);
    free(oldSorted);
    free(newSorted);
    return results;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single sort operation for window function ranking

⚡ Linearithmic

Space Complexity

O(n)

Space for result set and window function processing

⚡ Linearithmic Space

Constraints

1 ≤ number of teams ≤ 10⁴
Team names contain only letters and are unique
-10³ ≤ points_change ≤ 10³
Each team_id in TeamPoints appears exactly once in PointsChange
1 ≤ original points ≤ 10⁶

28.5K Views

Medium Frequency

~15 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Single Query with Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler