League Performance Metrics by Team

Medium

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 1

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 2

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 3

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.

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

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 |

[ {"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} ]

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 |

[ {"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} ]

[ {"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} ]