Seasonal League Table with Multi-Key Tie-Breaks

Medium

A competition analytics system stores one row per team per season in a relational table. Table: SeasonStats season_id: season identifier team_id: team identifier (unique within a season) team_name: team display name matches_played: total matches in the season wins: total wins draws: total draws losses: total losses goals_for: goals scored goals_against: goals conceded Task: For every team in every season, compute: points = wins * 3 + draws goal_difference = goals_for - goals_against position = 1-based ranking within each season using tie-breaks: points descending goal_difference descending team_name ascending Output requirements: Return columns in this exact order: season_id team_id team_name points goal_difference position Final ordering: season_id ascending position ascending team_name ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

SeasonStats:
| season_id | team_id | team_name         | matches_played | wins | draws | losses | goals_for | goals_against |
|-----------|---------|-------------------|----------------|------|-------|--------|-----------|---------------|
| 2031      | 1       | Aurora FC         | 38             | 20   | 10    | 8      | 70        | 40            |
| 2031      | 2       | Beacon United     | 38             | 20   | 10    | 8      | 64        | 38            |
| 2031      | 3       | Citadel City      | 38             | 18   | 12    | 8      | 60        | 41            |
| 2031      | 4       | Driftwood Rovers  | 38             | 18   | 12    | 8      | 55        | 36            |
| 2032      | 1       | Apex Athletic     | 38             | 25   | 7     | 6      | 82        | 35            |
| 2032      | 2       | Beacon United     | 38             | 24   | 8     | 6      | 75        | 40            |
| 2032      | 5       | Delta Town        | 38             | 24   | 8     | 6      | 77        | 39            |
| 2032      | 6       | Ember Park        | 38             | 18   | 10    | 10     | 61        | 50            |

Output

[
  {"season_id":2031,"team_id":1,"team_name":"Aurora FC","points":70,"goal_difference":30,"position":1},
  {"season_id":2031,"team_id":2,"team_name":"Beacon United","points":70,"goal_difference":26,"position":2},
  {"season_id":2031,"team_id":3,"team_name":"Citadel City","points":66,"goal_difference":19,"position":3},
  {"season_id":2031,"team_id":4,"team_name":"Driftwood Rovers","points":66,"goal_difference":19,"position":4},
  {"season_id":2032,"team_id":1,"team_name":"Apex Athletic","points":82,"goal_difference":47,"position":1},
  {"season_id":2032,"team_id":5,"team_name":"Delta Town","points":80,"goal_difference":38,"position":2},
  {"season_id":2032,"team_id":2,"team_name":"Beacon United","points":80,"goal_difference":35,"position":3},
  {"season_id":2032,"team_id":6,"team_name":"Ember Park","points":64,"goal_difference":11,"position":4}
]

Explanation

The ranking resets per season. Ties are resolved first by goal difference and then by team name.

Example 2

Input

SeasonStats:
| season_id | team_id | team_name | matches_played | wins | draws | losses | goals_for | goals_against |
|-----------|---------|-----------|----------------|------|-------|--------|-----------|---------------|
| 2040      | 10      | Alpha     | 10             | 5    | 2     | 3      | 14        | 10            |
| 2040      | 20      | Beta      | 10             | 5    | 2     | 3      | 13        | 9             |
| 2040      | 30      | Gamma     | 10             | 5    | 2     | 3      | 11        | 7             |

Output

[
  {"season_id":2040,"team_id":30,"team_name":"Gamma","points":17,"goal_difference":4,"position":1},
  {"season_id":2040,"team_id":10,"team_name":"Alpha","points":17,"goal_difference":4,"position":2},
  {"season_id":2040,"team_id":20,"team_name":"Beta","points":17,"goal_difference":4,"position":3}
]

Explanation

All teams tie on points and goal difference, so alphabetical team name order determines position.

Example 3

Input

SeasonStats:
| season_id | team_id | team_name    | matches_played | wins | draws | losses | goals_for | goals_against |
|-----------|---------|--------------|----------------|------|-------|--------|-----------|---------------|
| 2050      | 1       | Iron Vale    | 12             | 7    | 2     | 3      | 20        | 14            |
| 2050      | 2       | Jade Harbor  | 12             | 6    | 3     | 3      | 18        | 15            |
| 2051      | 1       | Iron Vale    | 12             | 2    | 4     | 6      | 11        | 19            |
| 2051      | 2       | Jade Harbor  | 12             | 3    | 2     | 7      | 10        | 18            |

Output

[
  {"season_id":2050,"team_id":1,"team_name":"Iron Vale","points":23,"goal_difference":6,"position":1},
  {"season_id":2050,"team_id":2,"team_name":"Jade Harbor","points":21,"goal_difference":3,"position":2},
  {"season_id":2051,"team_id":2,"team_name":"Jade Harbor","points":11,"goal_difference":-8,"position":1},
  {"season_id":2051,"team_id":1,"team_name":"Iron Vale","points":10,"goal_difference":-8,"position":2}
]

Explanation

Positions are computed independently per season, even for the same team ids.

Constraints

1 <= number of rows in SeasonStats <= 200000
(season_id, team_id) is unique
1 <= season_id <= 1000000000
1 <= team_id <= 1000000000
team_name is a non-empty string
0 <= matches_played <= 100
0 <= wins, draws, losses <= matches_played
0 <= goals_for, goals_against <= 500
points is computed as wins * 3 + draws
goal_difference is computed as goals_for - goals_against
position is 1-based ranking within each season
Tie-break order is points DESC, goal_difference DESC, team_name ASC
Return output columns exactly as specified
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

SeasonStats: | season_id | team_id | team_name | matches_played | wins | draws | losses | goals_for | goals_against | |-----------|---------|-------------------|----------------|------|-------|--------|-----------|---------------| | 2031 | 1 | Aurora FC | 38 | 20 | 10 | 8 | 70 | 40 | | 2031 | 2 | Beacon United | 38 | 20 | 10 | 8 | 64 | 38 | | 2031 | 3 | Citadel City | 38 | 18 | 12 | 8 | 60 | 41 | | 2031 | 4 | Driftwood Rovers | 38 | 18 | 12 | 8 | 55 | 36 | | 2032 | 1 | Apex Athletic | 38 | 25 | 7 | 6 | 82 | 35 | | 2032 | 2 | Beacon United | 38 | 24 | 8 | 6 | 75 | 40 | | 2032 | 5 | Delta Town | 38 | 24 | 8 | 6 | 77 | 39 | | 2032 | 6 | Ember Park | 38 | 18 | 10 | 10 | 61 | 50 |

[ {"season_id":2031,"team_id":1,"team_name":"Aurora FC","points":70,"goal_difference":30,"position":1}, {"season_id":2031,"team_id":2,"team_name":"Beacon United","points":70,"goal_difference":26,"position":2}, {"season_id":2031,"team_id":3,"team_name":"Citadel City","points":66,"goal_difference":19,"position":3}, {"season_id":2031,"team_id":4,"team_name":"Driftwood Rovers","points":66,"goal_difference":19,"position":4}, {"season_id":2032,"team_id":1,"team_name":"Apex Athletic","points":82,"goal_difference":47,"position":1}, {"season_id":2032,"team_id":5,"team_name":"Delta Town","points":80,"goal_difference":38,"position":2}, {"season_id":2032,"team_id":2,"team_name":"Beacon United","points":80,"goal_difference":35,"position":3}, {"season_id":2032,"team_id":6,"team_name":"Ember Park","points":64,"goal_difference":11,"position":4} ]

SeasonStats: | season_id | team_id | team_name | matches_played | wins | draws | losses | goals_for | goals_against | |-----------|---------|-----------|----------------|------|-------|--------|-----------|---------------| | 2040 | 10 | Alpha | 10 | 5 | 2 | 3 | 14 | 10 | | 2040 | 20 | Beta | 10 | 5 | 2 | 3 | 13 | 9 | | 2040 | 30 | Gamma | 10 | 5 | 2 | 3 | 11 | 7 |

[ {"season_id":2040,"team_id":30,"team_name":"Gamma","points":17,"goal_difference":4,"position":1}, {"season_id":2040,"team_id":10,"team_name":"Alpha","points":17,"goal_difference":4,"position":2}, {"season_id":2040,"team_id":20,"team_name":"Beta","points":17,"goal_difference":4,"position":3} ]

SeasonStats: | season_id | team_id | team_name | matches_played | wins | draws | losses | goals_for | goals_against | |-----------|---------|--------------|----------------|------|-------|--------|-----------|---------------| | 2050 | 1 | Iron Vale | 12 | 7 | 2 | 3 | 20 | 14 | | 2050 | 2 | Jade Harbor | 12 | 6 | 3 | 3 | 18 | 15 | | 2051 | 1 | Iron Vale | 12 | 2 | 4 | 6 | 11 | 19 | | 2051 | 2 | Jade Harbor | 12 | 3 | 2 | 7 | 10 | 18 |

[ {"season_id":2050,"team_id":1,"team_name":"Iron Vale","points":23,"goal_difference":6,"position":1}, {"season_id":2050,"team_id":2,"team_name":"Jade Harbor","points":21,"goal_difference":3,"position":2}, {"season_id":2051,"team_id":2,"team_name":"Jade Harbor","points":11,"goal_difference":-8,"position":1}, {"season_id":2051,"team_id":1,"team_name":"Iron Vale","points":10,"goal_difference":-8,"position":2} ]