LeetCode 1841 - League Statistics

LeetCode Problem 1841

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

This problem asks us to compute a complete league table from two database tables, Teams and Matches.

The Teams table contains the identity of each team in the league. Each row represents one team and includes a unique team_id and the corresponding team_name.

The Matches table contains the results of games played between teams. Every row describes one match, including:

• the home team
• the away team
• the number of goals scored by the home team
• the number of goals scored by the away team

From these matches, we must calculate league statistics for every team.

For each team, we need to compute:

• matches_played, total matches played as either home or away team

• points, based on football scoring rules:

• win = 3 points

• draw = 1 point

• loss = 0 points

• goal_for, total goals scored by the team

• goal_against, total goals conceded

• goal_diff, calculated as goal_for - goal_against

The final result must be sorted according to league ranking rules:

1. Higher points first
2. If points are tied, higher goal_diff first
3. If still tied, smaller team_name in lexicographical order

The important observation is that each match contributes statistics to two different teams simultaneously. A naive implementation might separately scan the entire Matches table for each team, but that would repeatedly process the same rows and become inefficient.

The problem guarantees that every (home_team_id, away_team_id) pair is unique, meaning no duplicate match records exist between the same home and away teams. It also guarantees valid team references.

Several edge cases are important:

• Teams may have zero matches played
• Teams may only appear as home or away teams
• Matches can end in draws
• Multiple teams can end with identical points and goal difference
• Teams can have negative goal difference
• A team can score zero goals across all matches

A correct solution must handle all of these situations cleanly.

Approaches

Brute Force Approach

The brute force solution processes each team independently.

For every team in the Teams table, we scan the entire Matches table and check whether the team participated in each match. If it did, we update:

• matches played
• goals scored
• goals conceded
• points earned

This approach is straightforward because each team's statistics are computed independently from scratch. The logic is easy to reason about because all calculations are localized to one team at a time.

However, this method repeatedly scans the same matches. If there are T teams and M matches, then we process the matches table T separate times, resulting in unnecessary repeated work.

Optimal Approach

The key insight is that every match contributes information to exactly two teams, the home team and the away team.

Instead of scanning all matches separately for each team, we can process every match once and immediately update the statistics for both participating teams.

We maintain an aggregate statistics structure indexed by team_id. As we iterate through matches:

• increment matches played for both teams
• add goals scored and conceded
• assign points based on the result

After all matches are processed, we join the computed statistics with the Teams table to retrieve team names and calculate goal_diff.

This reduces repeated work dramatically because each match is processed exactly once.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(T × M)	O(T)	Scans all matches separately for every team
Optimal	O(T + M)	O(T)	Processes each match once and aggregates statistics incrementally

Algorithm Walkthrough

1. Create a statistics structure indexed by team_id.

For each team, we store:

• matches played
• points
• goals for
• goals against

A hash map is ideal because it provides constant time updates and lookups. 2. Initialize every team's statistics to zero.

This is important because some teams may not appear in any match. Those teams must still appear in the final result. 3. Iterate through every match in the Matches table.

For each match:

• identify home team and away team
• read the goals scored by both teams

4. Update matches played.

Both teams participated in this match, so increment their matches_played count. 5. Update goals scored and conceded.

For the home team:

• add home_team_goals to goals for
• add away_team_goals to goals against

For the away team:

• add away_team_goals to goals for
• add home_team_goals to goals against

6. Assign points.

Compare the goals:

• if home goals > away goals, home team gets 3 points
• if away goals > home goals, away team gets 3 points
• otherwise both teams get 1 point

7. Build the final result.

For every team:

• retrieve accumulated statistics
• compute goal_diff = goal_for - goal_against
• attach the team name

8. Sort the result.

Apply the required ordering:

• descending points
• descending goal difference
• ascending team name

9. Return the sorted result table.

Why it works

The algorithm works because every match contributes independently and exactly once to the statistics of both participating teams. By updating both teams during a single pass over the matches table, we guarantee that all goals, matches, and points are counted correctly without duplication or omission.

Since the final sorting exactly follows the ranking rules described in the problem statement, the produced league table is correct.

Python Solution

SELECT
    t.team_name,
    COALESCE(stats.matches_played, 0) AS matches_played,
    COALESCE(stats.points, 0) AS points,
    COALESCE(stats.goal_for, 0) AS goal_for,
    COALESCE(stats.goal_against, 0) AS goal_against,
    COALESCE(stats.goal_for, 0) - COALESCE(stats.goal_against, 0) AS goal_diff
FROM Teams t
LEFT JOIN (
    SELECT
        team_id,
        COUNT(*) AS matches_played,
        SUM(points) AS points,
        SUM(goal_for) AS goal_for,
        SUM(goal_against) AS goal_against
    FROM (
        SELECT
            home_team_id AS team_id,
            home_team_goals AS goal_for,
            away_team_goals AS goal_against,
            CASE
                WHEN home_team_goals > away_team_goals THEN 3
                WHEN home_team_goals = away_team_goals THEN 1
                ELSE 0
            END AS points
        FROM Matches

        UNION ALL

        SELECT
            away_team_id AS team_id,
            away_team_goals AS goal_for,
            home_team_goals AS goal_against,
            CASE
                WHEN away_team_goals > home_team_goals THEN 3
                WHEN away_team_goals = home_team_goals THEN 1
                ELSE 0
            END AS points
        FROM Matches
    ) AS all_matches
    GROUP BY team_id
) AS stats
ON t.team_id = stats.team_id
ORDER BY
    points DESC,
    goal_diff DESC,
    team_name ASC;

The solution transforms each match into two rows using UNION ALL.

The first query represents the home team's perspective:

• goals scored become goal_for
• goals conceded become goal_against
• points are calculated according to the match outcome

The second query represents the away team's perspective with the same logic reversed.

After creating this unified representation, we aggregate by team_id using GROUP BY.

The outer query joins the aggregated statistics back to the Teams table so every team appears in the final result, even if it played zero matches. COALESCE converts NULL values into zeros for such teams.

Finally, the result is sorted according to the ranking rules.

Go Solution

// There is no Go solution for this problem because
// LeetCode 1841 is a SQL Database problem.

Since this is a database problem, LeetCode expects a SQL query rather than an implementation in a programming language like Go. Therefore, no Go-specific handling is necessary.

Worked Examples

Example 1

Input tables:

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

We process each match one by one.

Match 1: Ajax vs Dortmund, score 0-1

Team	Matches	Points	GF	GA
Ajax	1	0	0	1
Dortmund	1	3	1	0
Arsenal	0	0	0	0

Dortmund wins and gets 3 points.

Match 2: Ajax vs Arsenal, score 3-3

Team	Matches	Points	GF	GA
Ajax	2	1	3	4
Dortmund	1	3	1	0
Arsenal	1	1	3	3

Draw, both teams receive 1 point.

Match 3: Dortmund vs Ajax, score 5-2

Team	Matches	Points	GF	GA
Ajax	3	1	5	9
Dortmund	2	6	6	2
Arsenal	1	1	3	3

Dortmund wins again.

Match 4: Arsenal vs Ajax, score 0-0

Team	Matches	Points	GF	GA
Ajax	4	2	5	9
Dortmund	2	6	6	2
Arsenal	2	2	3	3

Both teams receive 1 point.

Now compute goal difference:

Team	Goal Diff
Dortmund	6 - 2 = 4
Arsenal	3 - 3 = 0
Ajax	5 - 9 = -4

Sorting rules produce:

1. Dortmund
2. Arsenal
3. Ajax

Complexity Analysis

Measure	Complexity	Explanation
Time	O(T + M)	Each team and match is processed once
Space	O(T)	Aggregated statistics stored per team

The query scans the Matches table once for the home-team projection and once for the away-team projection. This is still linear in the number of matches. Aggregation requires storage proportional to the number of teams.

Test Cases

# Example case from the problem
# Validates normal ranking and goal difference ordering

# Teams:
# Ajax, Dortmund, Arsenal

# Dortmund should rank first with 6 points
# Arsenal should beat Ajax because of better goal difference

# Team with no matches
# Should still appear with all zero statistics

# Teams:
# A, B

# No matches exist

# All matches are draws
# Every team should receive equal points

# A vs B = 1-1
# B vs C = 2-2

# Negative goal difference
# Team loses heavily and should have negative diff

# A vs B = 0-5

# Lexicographical tie breaker
# Same points and same goal difference

# Alpha vs Beta = 0-0

# Team appears only as away team
# Statistics should still be counted correctly

# A vs B = 1-2
# C vs B = 0-1

# High scoring matches
# Ensures goals are aggregated correctly

# A vs B = 10-8
# B vs A = 7-6

# Multiple identical point totals
# Goal difference should decide ranking

# A vs B = 2-0
# B vs C = 3-0
# C vs A = 1-0

Test	Why
Example from statement	Validates overall correctness
Team with no matches	Ensures LEFT JOIN and COALESCE work
All draws	Validates draw point assignment
Negative goal difference	Ensures subtraction logic is correct
Lexicographical tie breaker	Confirms final ordering condition
Away-only participation	Ensures away teams are counted properly
High scoring matches	Validates goal aggregation
Equal points with different goal difference	Confirms secondary sorting rule

Edge Cases

Teams With No Matches

A team may exist in the Teams table but never appear in the Matches table. A naive inner join would exclude such teams entirely.

This solution uses a LEFT JOIN from Teams to the aggregated statistics table. Missing values are converted to zero using COALESCE, ensuring every team appears in the final standings.

Draw Matches

Draws are easy to mishandle because both teams receive points simultaneously.

The implementation explicitly checks equality of goals:

• home goals equal away goals
• both teams receive exactly 1 point

This guarantees correct handling of tied matches.

Teams Tied on Points

Two or more teams may finish with identical point totals. The problem specifies additional tie breakers:

• higher goal difference first
• lexicographical team name ordering afterward

The final ORDER BY clause applies these conditions in the exact required order, ensuring deterministic and correct rankings.