Watch 4 video solutions for League Statistics, a medium level problem involving Database. This walkthrough by Everyday Data Science has 1,764 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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.
| 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 |