League Table Points and Shared Rank Positions

Medium

An operations team maintains a football standings table where each row stores a team's season totals. Table: TeamStats team_id: unique team identifier team_name: team display name matches_played: total matches played wins: number of wins draws: number of draws losses: number of losses Scoring policy: Win gives 3 points Draw gives 1 point Loss gives 0 points Task: For each team, compute: points = wins * 3 + draws position = ranking by points in descending order Ranking rule: Teams with equal points share the same position. Ranking uses competition ranking (with gaps after ties). Example: points [20, 20, 18] map to positions [1, 1, 3]. Output requirements: Return exactly these columns in this order: team_id, team_name, points, position Sort output by points descending, then team_name ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

TeamStats:
| team_id | team_name       | matches_played | wins | draws | losses |
|---------|-----------------|----------------|------|-------|--------|
| 2       | Liverpool       | 10             | 6    | 2     | 2      |
| 1       | Manchester City | 10             | 6    | 2     | 2      |
| 4       | Arsenal         | 10             | 4    | 4     | 2      |
| 3       | Chelsea         | 10             | 5    | 3     | 2      |
| 5       | Tottenham       | 10             | 3    | 5     | 2      |

Output

[
  {"team_id":2,"team_name":"Liverpool","points":20,"position":1},
  {"team_id":1,"team_name":"Manchester City","points":20,"position":1},
  {"team_id":3,"team_name":"Chelsea","points":18,"position":3},
  {"team_id":4,"team_name":"Arsenal","points":16,"position":4},
  {"team_id":5,"team_name":"Tottenham","points":14,"position":5}
]

Explanation

Liverpool and Manchester City tie on points and both receive position 1. The next team is position 3 because competition ranking skips positions after ties.

Example 2

Input

TeamStats:
| team_id | team_name      | matches_played | wins | draws | losses |
|---------|----------------|----------------|------|-------|--------|
| 101     | Alpha Athletic | 13             | 1    | 9     | 3      |
| 100     | Zenith Borough | 8              | 2    | 6     | 0      |
| 103     | Beta City      | 10             | 5    | 0     | 5      |
| 102     | Delta Rangers  | 15             | 1    | 12    | 2      |
| 104     | Orion United   | 7              | 2    | 3     | 2      |

Output

[
  {"team_id":103,"team_name":"Beta City","points":15,"position":1},
  {"team_id":102,"team_name":"Delta Rangers","points":15,"position":1},
  {"team_id":101,"team_name":"Alpha Athletic","points":12,"position":3},
  {"team_id":100,"team_name":"Zenith Borough","points":12,"position":3},
  {"team_id":104,"team_name":"Orion United","points":9,"position":5}
]

Explanation

Two independent tie groups exist. Teams are still listed by points first and team_name second.

Example 3

Input

TeamStats:
| team_id | team_name     | matches_played | wins | draws | losses |
|---------|---------------|----------------|------|-------|--------|
| 202     | Cobalt Rovers | 17             | 5    | 12    | 0      |
| 201     | Lime Forge    | 19             | 6    | 9     | 4      |
| 200     | North Harbor  | 14             | 7    | 6     | 1      |
| 203     | Amber Pilots  | 8              | 8    | 0     | 0      |

Output

[
  {"team_id":202,"team_name":"Cobalt Rovers","points":27,"position":1},
  {"team_id":201,"team_name":"Lime Forge","points":27,"position":1},
  {"team_id":200,"team_name":"North Harbor","points":27,"position":1},
  {"team_id":203,"team_name":"Amber Pilots","points":24,"position":4}
]

Explanation

Three teams tie for first place. The next team is position 4 because three teams are ranked above it.

Constraints

1 <= number of rows in TeamStats <= 200000
1 <= team_id <= 1000000000
team_id is unique
team_name is a non-empty string
0 <= wins, draws, losses <= 1000
matches_played = wins + draws + losses
points must be computed as wins * 3 + draws
position must use competition ranking semantics
Output columns must be exactly: team_id, team_name, points, position
Final order must be points DESC, team_name ASC
SQL submissions are evaluated using SQLite 3.27.2
Pandas submissions must return a DataFrame with required columns

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|-----------------|----------------|------|-------|--------| | 2 | Liverpool | 10 | 6 | 2 | 2 | | 1 | Manchester City | 10 | 6 | 2 | 2 | | 4 | Arsenal | 10 | 4 | 4 | 2 | | 3 | Chelsea | 10 | 5 | 3 | 2 | | 5 | Tottenham | 10 | 3 | 5 | 2 |

[ {"team_id":2,"team_name":"Liverpool","points":20,"position":1}, {"team_id":1,"team_name":"Manchester City","points":20,"position":1}, {"team_id":3,"team_name":"Chelsea","points":18,"position":3}, {"team_id":4,"team_name":"Arsenal","points":16,"position":4}, {"team_id":5,"team_name":"Tottenham","points":14,"position":5} ]

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|----------------|----------------|------|-------|--------| | 101 | Alpha Athletic | 13 | 1 | 9 | 3 | | 100 | Zenith Borough | 8 | 2 | 6 | 0 | | 103 | Beta City | 10 | 5 | 0 | 5 | | 102 | Delta Rangers | 15 | 1 | 12 | 2 | | 104 | Orion United | 7 | 2 | 3 | 2 |

[ {"team_id":103,"team_name":"Beta City","points":15,"position":1}, {"team_id":102,"team_name":"Delta Rangers","points":15,"position":1}, {"team_id":101,"team_name":"Alpha Athletic","points":12,"position":3}, {"team_id":100,"team_name":"Zenith Borough","points":12,"position":3}, {"team_id":104,"team_name":"Orion United","points":9,"position":5} ]

TeamStats: | team_id | team_name | matches_played | wins | draws | losses | |---------|---------------|----------------|------|-------|--------| | 202 | Cobalt Rovers | 17 | 5 | 12 | 0 | | 201 | Lime Forge | 19 | 6 | 9 | 4 | | 200 | North Harbor | 14 | 7 | 6 | 1 | | 203 | Amber Pilots | 8 | 8 | 0 | 0 |

[ {"team_id":202,"team_name":"Cobalt Rovers","points":27,"position":1}, {"team_id":201,"team_name":"Lime Forge","points":27,"position":1}, {"team_id":200,"team_name":"North Harbor","points":27,"position":1}, {"team_id":203,"team_name":"Amber Pilots","points":24,"position":4} ]