League Statistics - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min read

Problem Statement

Table: Teams

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| team_id        | int     |
| team_name      | varchar |
+----------------+---------+
team_id is the column with unique values for this table.
Each row contains information about one team in the league.

Table: Matches

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| home_team_id    | int     |
| away_team_id    | int     |
| home_team_goals | int     |
| away_team_goals | int     |
+-----------------+---------+
(home_team_id, away_team_id) is the primary key (combination of columns with unique values) for this table.
Each row contains information about one match.
home_team_goals is the number of goals scored by the home team.
away_team_goals is the number of goals scored by the away team.
The winner of the match is the team with the higher number of goals.

Write a solution to report the statistics of the league. The statistics should be built using the played matches where the winning team gets three points and the losing team gets no points. If a match ends with a draw, both teams get one point.

Each row of the result table should contain:

team_name - The name of the team in the Teams table.
matches_played - The number of matches played as either a home or away team.
points - The total points the team has so far.
goal_for - The total number of goals scored by the team across all matches.
goal_against - The total number of goals scored by opponent teams against this team across all matches.
goal_diff - The result of goal_for - goal_against.

Return the result table ordered by points in descending order. If two or more teams have the same points, order them by goal_diff in descending order. If there is still a tie, order them by team_name in lexicographical order.

The result format is in the following example.

Example 1:

Input: 
Teams table:
+---------+-----------+
| team_id | team_name |
+---------+-----------+
| 1       | Ajax      |
| 4       | Dortmund  |
| 6       | Arsenal   |
+---------+-----------+
Matches table:
+--------------+--------------+-----------------+-----------------+
| 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 | matches_played | points | goal_for | goal_against | goal_diff |
+-----------+----------------+--------+----------+--------------+-----------+
| Dortmund  | 2              | 6      | 6        | 2            | 4         |
| Arsenal   | 2              | 2      | 3        | 3            | 0         |
| Ajax      | 4              | 2      | 5        | 9            | -4        |
+-----------+----------------+--------+----------+--------------+-----------+
Explanation: 
Ajax (team_id=1) played 4 matches: 2 losses and 2 draws. Total points = 0 + 0 + 1 + 1 = 2.
Dortmund (team_id=4) played 2 matches: 2 wins. Total points = 3 + 3 = 6.
Arsenal (team_id=6) played 2 matches: 2 draws. Total points = 1 + 1 = 2.
Dortmund is the first team in the table. Ajax and Arsenal have the same points, but since Arsenal has a higher goal_diff than Ajax, Arsenal comes before Ajax in the table.

Approach Overview

Problem Overview: You are given football match results where each row stores the home team, away team, and the goals scored by each. The task is to compute league statistics for every team: matches played, total points, goals for, goals against, and goal difference. The final table must be sorted by points, goal difference, and team name.

Approach 1: Normalize Matches with UNION ALL + Aggregation (O(n) time, O(n) space)

The cleanest strategy is to convert every match into two rows: one representing the home team perspective and another for the away team. Use UNION ALL to build a derived table containing team_id, goals scored, goals conceded, and points earned in that match. Points are calculated with a CASE expression: 3 for a win, 1 for a draw, 0 for a loss. After normalization, run a GROUP BY team_id to aggregate totals such as matches played (COUNT(*)), goals for (SUM(goals_for)), goals against, and total points. Finally, join the aggregated result with the Teams table to retrieve team names and compute goal_diff = goals_for - goals_against. Sorting by points DESC, goal_diff DESC, and team_name ASC produces the league table. This approach scans the matches once and uses standard SQL aggregation patterns.

Approach 2: Conditional Aggregation with Direct Joins (O(n) time, O(1) extra space)

Another option keeps the matches table unchanged and calculates statistics using conditional aggregation. Join Teams with Matches where the team appears as either home or away. Inside the aggregation, compute metrics using CASE expressions. For example, goals scored becomes CASE WHEN team_id = home_team_id THEN home_team_goals ELSE away_team_goals END, while goals conceded flips the columns. Points are determined by comparing goals and awarding 3/1/0 accordingly. The COUNT of joined rows gives matches played. This avoids constructing an intermediate table but produces more complex conditional expressions. It relies heavily on database query logic and aggregation functions.

Recommended for interviews: The UNION ALL normalization approach is easier to reason about and mirrors how analysts transform match data before aggregation. Interviewers typically expect you to convert each match into two team-centric rows and then apply straightforward GROUP BY logic. Conditional aggregation works but is harder to read and debug. Showing the normalized approach demonstrates strong SQL modeling and aggregation skills.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
UNION ALL Normalization + GROUP BY	O(n)	O(n)	Best general solution; simple aggregation after transforming matches into team-level rows
Conditional Aggregation with Joins	O(n)	O(1)	When avoiding derived tables or UNION; useful if the schema must remain unchanged

Video Solution

LeetCode Medium 1841 "League Statistics" Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,808 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is League Statistics easy or hard?

League Statistics is generally rated Medium difficulty. The challenge comes from modeling match data correctly and computing statistics from two team perspectives in each row using SQL aggregation.

League Statistics Python/Java solution

This problem is primarily solved using SQL rather than Python or Java. In coding interviews, the expected answer is a MySQL or PostgreSQL query that aggregates match data and calculates points and goal differences.

How to solve League Statistics in O(n)?

Create a derived table using UNION ALL that represents each team's perspective for every match. Assign goals_for, goals_against, and points with CASE expressions. Then aggregate with GROUP BY team_id to compute totals and join with the Teams table for names. Sorting the final result produces the league standings.

What is the best approach for League Statistics?

The most common solution converts each match into two rows using UNION ALL so every team appears once per match. After normalization, a GROUP BY aggregates matches played, goals for, goals against, and points. This approach keeps the SQL readable and runs in O(n) time over the matches table.

Is League Statistics asked at Google/Amazon/Meta?

League table aggregation problems appear frequently in SQL interview rounds at companies like Amazon, Meta, and analytics-heavy teams at Google. They test SQL fundamentals such as CASE expressions, UNION ALL, and GROUP BY aggregation.

What data structure is used in League Statistics?

The problem relies on relational database tables and SQL aggregation. Core constructs include UNION ALL, CASE expressions, and GROUP BY operations to compute per-team statistics from match records.

What is the time complexity of League Statistics?

The query typically runs in O(n) time where n is the number of rows in the Matches table. Each match is scanned once (or twice logically after UNION ALL), and aggregation with GROUP BY computes totals per team. Space complexity is O(n) for the derived table created by UNION ALL.

Ready to solve this problem?

Practice League Statistics with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance53.4%

Approaches1

Reading time5 min

Practice this problem

Open in Editor