League Performance Metrics by Team

MEDIUM20 pts

A football analytics platform stores club metadata and completed match results. The operations team needs a standings-style report that combines points and goal statistics for every club.

Table: teams

team_id: unique identifier of a team
team_name: display name of a team

Table: matches

home_team_id: host team id
away_team_id: visiting team id
home_team_goals: goals scored by the home team
away_team_goals: goals scored by the away team

Scoring policy:

Win => 3 points for the winner
Draw => 1 point to each team
Loss => 0 points

Task: For each team in teams, compute:

matches_played: count of matches where the team appears as home or away
points: total points from all matches
goal_for: total goals scored by the team
goal_against: total goals conceded by the team
goal_diff: goal_for - goal_against

Output requirements:

Return exactly these columns in this order: team_name, matches_played, points, goal_for, goal_against, goal_diff
Include teams with no matches, using 0 for all numeric metrics.
Sort by points in descending order.
If points tie, sort by goal_diff in descending order.
If there is still a tie, sort by team_name in ascending lexicographical order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

teams:
| team_id | team_name |
|---------|-----------|
| 1       | Ajax      |
| 4       | Dortmund  |
| 6       | Arsenal   |

matches:
| home_team_id | away_team_id | home_team_goals | away_team_goals |
|--------------|--------------|-----------------|-----------------|
| 1            | 4            | 0               | 1               |
| 1            | 6            | 3               | 3               |
| 4            | 1            | 5               | 2               |
| 6            | 1            | 0               | 0               |

Output

[
  {"team_name":"Dortmund","matches_played":2,"points":6,"goal_for":6,"goal_against":2,"goal_diff":4},
  {"team_name":"Arsenal","matches_played":2,"points":2,"goal_for":3,"goal_against":3,"goal_diff":0},
  {"team_name":"Ajax","matches_played":4,"points":2,"goal_for":5,"goal_against":9,"goal_diff":-4}
]

Explanation

Dortmund wins both matches for 6 points. Ajax and Arsenal both have 2 points, so goal difference decides their order.

Example

Input

teams:
| team_id | team_name      |
|---------|----------------|
| 10      | Harbor City    |
| 20      | River Athletic |
| 30      | Summit United  |
| 40      | Metro Rovers   |

matches:
| home_team_id | away_team_id | home_team_goals | away_team_goals |
|--------------|--------------|-----------------|-----------------|
| 10           | 20           | 1               | 0               |
| 30           | 10           | 2               | 2               |
| 20           | 30           | 3               | 1               |

Output

[
  {"team_name":"Harbor City","matches_played":2,"points":4,"goal_for":3,"goal_against":2,"goal_diff":1},
  {"team_name":"River Athletic","matches_played":2,"points":3,"goal_for":3,"goal_against":2,"goal_diff":1},
  {"team_name":"Summit United","matches_played":2,"points":1,"goal_for":3,"goal_against":5,"goal_diff":-2},
  {"team_name":"Metro Rovers","matches_played":0,"points":0,"goal_for":0,"goal_against":0,"goal_diff":0}
]

Explanation

Metro Rovers has no recorded matches and is still included with zeroed statistics.

Example

Input

teams:
| team_id | team_name |
|---------|-----------|
| 5       | Alpha FC  |
| 7       | Beta FC   |
| 9       | Gamma FC  |

matches:
| (no rows) |

Output

[
  {"team_name":"Alpha FC","matches_played":0,"points":0,"goal_for":0,"goal_against":0,"goal_diff":0},
  {"team_name":"Beta FC","matches_played":0,"points":0,"goal_for":0,"goal_against":0,"goal_diff":0},
  {"team_name":"Gamma FC","matches_played":0,"points":0,"goal_for":0,"goal_against":0,"goal_diff":0}
]

Explanation

With no matches available, every team has zero points and goals. Final ordering is by team_name.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in teams <= 200000
0 <= number of rows in matches <= 400000
1 <= team_id <= 1000000000
team_id is unique in teams
team_name is a non-empty string and unique in teams
home_team_id and away_team_id always exist in teams
home_team_id != away_team_id for every match
(home_team_id, away_team_id) is unique in matches
0 <= home_team_goals, away_team_goals <= 20
goal_diff must be computed as goal_for - goal_against
Sort by points DESC, goal_diff DESC, team_name ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

teams =

[{"team_id":1,"team_name":"Ajax"},{"team_id":4,"team_name":"Dortmund"},{"team_id":6,"team_name":"Arsenal"}]

matches =

[{"home_team_id":1,"away_team_id":4,"home_team_goals":0,"away_team_goals":1},{"home_team_id":1,"away_team_id":6,"home_team_goals":3,"away_team_goals":3},{"home_team_id":4,"away_team_id":1,"home_team_goals":5,"away_team_goals":2},{"home_team_id":6,"away_team_id":1,"home_team_goals":0,"away_team_goals":0}]

League Performance Metrics by Team

Hints

League Performance Metrics by Team

Hints