Table: Teams
+---------------+----------+ | Column Name | Type | +---------------+----------+ | team_id | int | | team_name | varchar | +---------------+----------+ team_id is the column with unique values of this table. Each row of this table represents a single football team.
Table: Matches
+---------------+---------+ | Column Name | Type | +---------------+---------+ | match_id | int | | host_team | int | | guest_team | int | | host_goals | int | | guest_goals | int | +---------------+---------+ match_id is the column of unique values of this table. Each row is a record of a finished match between two different teams. Teams host_team and guest_team are represented by their IDs in the Teams table (team_id), and they scored host_goals and guest_goals goals, respectively.
You would like to compute the scores of all teams after all matches. Points are awarded as follows:
Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.
Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.
The result format is in the following example.
Example 1:
Input: Teams table: +-----------+--------------+ | team_id | team_name | +-----------+--------------+ | 10 | Leetcode FC | | 20 | NewYork FC | | 30 | Atlanta FC | | 40 | Chicago FC | | 50 | Toronto FC | +-----------+--------------+ Matches table: +------------+--------------+---------------+-------------+--------------+ | 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 | team_name | num_points | +------------+--------------+---------------+ | 10 | Leetcode FC | 7 | | 20 | NewYork FC | 3 | | 50 | Toronto FC | 3 | | 30 | Atlanta FC | 1 | | 40 | Chicago FC | 0 | +------------+--------------+---------------+
Problem Overview: You have two tables: Teams and Matches. Each match records the home team, away team, and goals scored. The task is to compute the total tournament points for every team (3 for a win, 1 for a draw, 0 for a loss) and return them sorted by points and goal difference.
Approach 1: LEFT JOIN + GROUP BY + CASE Expression (O(n) time, O(t) space)
This approach scans the Teams table and joins it with Matches using a LEFT JOIN. The join condition checks whether the team participated in the match as either the home or away team. A CASE expression determines the points earned from each match by comparing goals scored. Aggregation with GROUP BY team_id sums the points and goals across all matches played by that team.
The key insight is handling both perspectives of a match (home and away) inside conditional expressions. You compute goals scored, goals conceded, and points differently depending on whether the team appears as home_team_id or away_team_id. Because the query processes each match only once during aggregation, the runtime is linear with respect to the number of rows in Matches. This is a common pattern when solving database problems that require summarizing event data per entity.
Approach 2: UNION ALL Match Expansion + Aggregation (O(n) time, O(n) space)
Another way to structure the query is to convert each match into two rows: one representing the home team’s perspective and another for the away team. Use UNION ALL to combine these rows into a single dataset containing team_id, goals_for, goals_against, and calculated points. After this transformation, the problem becomes a straightforward aggregation using GROUP BY team_id.
This design simplifies the scoring logic because each row represents exactly one team in one match. A simple CASE statement compares goals_for and goals_against to assign 3, 1, or 0 points. The final query then joins the aggregated results back to the Teams table to include teams that may not have played any matches. This pattern is widely used in SQL analytics queries where event data must be normalized before aggregation.
Recommended for interviews: The LEFT JOIN + GROUP BY + CASE approach is usually expected. It demonstrates strong command of conditional aggregation, a core skill in GROUP BY queries. The UNION ALL approach is equally valid and often easier to reason about, but interviewers typically prefer the single-pass aggregation pattern because it shows you can handle conditional logic directly inside SQL aggregations.
We can join the Teams table and the Matches table using a left join, where the join condition is team_id = host_team OR team_id = guest_team, to obtain all the match information for each team.
Next, we group by team_id and use a CASE expression to calculate the points for each team according to the following rules:
3 points to the team's score.3 points to the team's score.1 point to the team's score.Finally, we sort the result by points in descending order, and if the points are the same, we sort by team_id in ascending order.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + CASE + GROUP BY | O(n) | O(t) | Best general solution. Efficient conditional aggregation while scanning matches once. |
| UNION ALL Match Expansion + Aggregation | O(n) | O(n) | Useful when simplifying scoring logic by representing each team-match as its own row. |
LeetCode 1212: Team Scores in Football Tournament [SQL] • Frederik Müller • 5,684 views views
Watch 3 more video solutions →Practice Team Scores in Football Tournament with our built-in code editor and test cases.
Practice on FleetCode