League Standings from Match Results

Medium

A football competition stores roster and match data in two relational tables. Teams table: team_id: unique identifier of a team team_name: display name of the team Matches table: match_id: unique identifier of a finished match host_team: home team id guest_team: away team id host_goals: goals scored by the home team guest_goals: goals scored by the away team Scoring rules: A win awards 3 points to the winner A draw awards 1 point to each team A loss awards 0 points Task: Compute final points for every team after all matches. Output requirements: Return exactly these columns in this order: team_id team_name num_points Sort results by num_points in descending order. If two teams have the same num_points, sort those teams by team_id in ascending order. Teams that never appear in matches must still be included with 0 points. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

teams:
| team_id | team_name        |
|---------|------------------|
| 10      | Leaders FC       |
| 20      | Harbor United    |
| 30      | Capital Athletic |
| 40      | Midtown City     |
| 50      | Lakeside Rovers  |

matches:
| match_id | host_team | guest_team | host_goals | guest_goals |
|----------|-----------|------------|------------|-------------|
| 1        | 10        | 20         | 3          | 0           |
| 2        | 30        | 10         | 2          | 2           |
| 3        | 10        | 50         | 5          | 1           |
| 4        | 20        | 30         | 1          | 0           |
| 5        | 50        | 30         | 1          | 0           |

Output

[
  {"team_id":10,"team_name":"Leaders FC","num_points":7},
  {"team_id":20,"team_name":"Harbor United","num_points":3},
  {"team_id":50,"team_name":"Lakeside Rovers","num_points":3},
  {"team_id":30,"team_name":"Capital Athletic","num_points":1},
  {"team_id":40,"team_name":"Midtown City","num_points":0}
]

Explanation

Team 10 earns a win, a draw, and another win for 7 total points. Team 40 has no matches and remains at 0.

Example 2

Input

teams:
| team_id | team_name   |
|---------|-------------|
| 101     | North Stars |
| 205     | Metro Forge |
| 309     | River Comets|

matches:
| match_id | host_team | guest_team | host_goals | guest_goals |
|----------|-----------|------------|------------|-------------|
| 11       | 101       | 205        | 1          | 1           |
| 12       | 205       | 309        | 2          | 2           |
| 13       | 309       | 101        | 0          | 0           |

Output

[
  {"team_id":101,"team_name":"North Stars","num_points":2},
  {"team_id":205,"team_name":"Metro Forge","num_points":2},
  {"team_id":309,"team_name":"River Comets","num_points":2}
]

Explanation

All matches are draws, so every team gains one point per appearance and all totals tie at 2.

Example 3

Input

teams:
| team_id | team_name    |
|---------|--------------|
| 7       | Blue Harbor  |
| 8       | Green Harbor |
| 11      | Red Harbor   |
| 19      | Gold Harbor  |

matches:
| match_id | host_team | guest_team | host_goals | guest_goals |
|----------|-----------|------------|------------|-------------|
| 21       | 7         | 8          | 2          | 1           |
| 22       | 11        | 7          | 4          | 0           |
| 23       | 8         | 11         | 2          | 2           |

Output

[
  {"team_id":11,"team_name":"Red Harbor","num_points":4},
  {"team_id":7,"team_name":"Blue Harbor","num_points":3},
  {"team_id":8,"team_name":"Green Harbor","num_points":1},
  {"team_id":19,"team_name":"Gold Harbor","num_points":0}
]

Explanation

Team 11 gets one win and one draw. Team 19 never plays and remains in the table with zero points.

Constraints

1 <= number of rows in teams <= 100000
0 <= number of rows in matches <= 300000
team_id values are unique
match_id values are unique
host_team and guest_team always reference valid team_id values
host_team != guest_team for every match
0 <= host_goals, guest_goals <= 20
team_name is a non-empty string
A win gives 3 points, draw gives 1 point each, loss gives 0 points
Return columns exactly as: team_id, team_name, num_points
Sort by num_points DESC, then team_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

teams: | team_id | team_name | |---------|------------------| | 10 | Leaders FC | | 20 | Harbor United | | 30 | Capital Athletic | | 40 | Midtown City | | 50 | Lakeside Rovers | matches: | match_id | host_team | guest_team | host_goals | guest_goals | |----------|-----------|------------|------------|-------------| | 1 | 10 | 20 | 3 | 0 | | 2 | 30 | 10 | 2 | 2 | | 3 | 10 | 50 | 5 | 1 | | 4 | 20 | 30 | 1 | 0 | | 5 | 50 | 30 | 1 | 0 |

[ {"team_id":10,"team_name":"Leaders FC","num_points":7}, {"team_id":20,"team_name":"Harbor United","num_points":3}, {"team_id":50,"team_name":"Lakeside Rovers","num_points":3}, {"team_id":30,"team_name":"Capital Athletic","num_points":1}, {"team_id":40,"team_name":"Midtown City","num_points":0} ]

teams: | team_id | team_name | |---------|-------------| | 101 | North Stars | | 205 | Metro Forge | | 309 | River Comets| matches: | match_id | host_team | guest_team | host_goals | guest_goals | |----------|-----------|------------|------------|-------------| | 11 | 101 | 205 | 1 | 1 | | 12 | 205 | 309 | 2 | 2 | | 13 | 309 | 101 | 0 | 0 |

teams: | team_id | team_name | |---------|--------------| | 7 | Blue Harbor | | 8 | Green Harbor | | 11 | Red Harbor | | 19 | Gold Harbor | matches: | match_id | host_team | guest_team | host_goals | guest_goals | |----------|-----------|------------|------------|-------------| | 21 | 7 | 8 | 2 | 1 | | 22 | 11 | 7 | 4 | 0 | | 23 | 8 | 11 | 2 | 2 |

[ {"team_id":11,"team_name":"Red Harbor","num_points":4}, {"team_id":7,"team_name":"Blue Harbor","num_points":3}, {"team_id":8,"team_name":"Green Harbor","num_points":1}, {"team_id":19,"team_name":"Gold Harbor","num_points":0} ]