Skip to main content
Talentd

Team Scores in Football Tournament - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase6 min readAsked at: Wayfair
Practice this problem

Problem Statement

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:
  • A team receives three points if they win a match (i.e., Scored more goals than the opponent team).
  • A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).
  • A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).

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             |
+------------+--------------+---------------+

Approach Overview

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.

Solution

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:

  • If the team is the host team and has more goals than the guest team, add 3 points to the team's score.
  • If the team is the guest team and has more goals than the host team, add 3 points to the team's score.
  • If the host team and the guest team have the same number of goals, add 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.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
LEFT JOIN + CASE + GROUP BYO(n)O(t)Best general solution. Efficient conditional aggregation while scanning matches once.
UNION ALL Match Expansion + AggregationO(n)O(n)Useful when simplifying scoring logic by representing each team-match as its own row.

Video Solution

LeetCode 1212: Team Scores in Football Tournament [SQL] • Frederik Müller • 5,714 views views

Watch 5 more video solutions →

Frequently Asked Questions

Is Team Scores in Football Tournament easy or hard?
The problem is rated Medium because it requires careful conditional aggregation and handling teams appearing in two roles within the same table (home and away). Understanding JOIN logic and CASE-based scoring is the main challenge.
Team Scores in Football Tournament Python/Java solution
This is primarily a SQL database problem rather than a Python or Java algorithm problem. The typical implementation uses SQL queries with LEFT JOIN, CASE expressions, and GROUP BY to compute team points and goal statistics directly inside the database engine.
How to solve Team Scores in Football Tournament in O(n)?
Use conditional aggregation. Join Teams with Matches using a LEFT JOIN so every team appears in the result. Inside SUM expressions, apply CASE conditions that check whether the team is the home or away team and award 3 points for wins, 1 for draws, and 0 for losses. GROUP BY the team_id to compute totals in a single scan.
What is the best approach for Team Scores in Football Tournament?
The most common solution uses LEFT JOIN with conditional aggregation. Join the Teams table with Matches, then use CASE expressions to calculate points based on match results. Aggregate using GROUP BY team_id to sum points, goals scored, and goals conceded. This approach processes matches in a single pass with O(n) time complexity.
Is Team Scores in Football Tournament asked at Google/Amazon/Meta?
Database aggregation problems similar to this appear frequently in SQL interviews at companies like Amazon, Meta, and other data-heavy teams. The focus is usually on JOIN logic, conditional CASE expressions, and correct aggregation using GROUP BY.
What data structure is used in Team Scores in Football Tournament?
The problem relies on relational tables and SQL aggregation rather than traditional data structures. Conceptually, the GROUP BY operation acts like a hash map where each team_id accumulates totals such as points, goals scored, and goals conceded.
What is the time complexity of Team Scores in Football Tournament?
The optimal SQL solution runs in O(n) time where n is the number of rows in the Matches table. The database scans match records once and aggregates results per team using GROUP BY. Space complexity is typically O(t) for storing intermediate aggregates for each team.

Ready to solve this problem?

Practice Team Scores in Football Tournament with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary