00:00:00

Description

Editorial

League Standings from Match Results

MEDIUM20 pts

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:
1. team_id
2. team_name
3. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

teams =

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

matches =

[{"match_id":1,"host_team":10,"guest_team":20,"host_goals":3,"guest_goals":0},{"match_id":2,"host_team":30,"guest_team":10,"host_goals":2,"guest_goals":2},{"match_id":3,"host_team":10,"guest_team":50,"host_goals":5,"guest_goals":1},{"match_id":4,"host_team":20,"guest_team":30,"host_goals":1,"guest_goals":0},{"match_id":5,"host_team":50,"guest_team":30,"host_goals":1,"guest_goals":0}]

Loading problem...

101

00:00:00

Description

Editorial

League Standings from Match Results

MEDIUM20 pts

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:
1. team_id
2. team_name
3. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

teams =

matches =

League Standings from Match Results

Hints

League Standings from Match Results

Hints