League Statistics - Practice Coding Problems

League Statistics - Problem

Database Medium

You're tasked with building a comprehensive league management system that calculates team statistics based on match results. Given two database tables - one containing team information and another with match data - you need to compute detailed performance metrics for each team.

The Teams table contains basic team information:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| team_id        | int     |
| team_name      | varchar |
+----------------+---------+

The Matches table records every game played:

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| home_team_id    | int     |
| away_team_id    | int     |
| home_team_goals | int     |
| away_team_goals | int     |
+-----------------+---------+

Scoring System:

Win: 3 points
Draw: 1 point each
Loss: 0 points

Your task is to generate a comprehensive statistics table containing:

team_name - The team's name
matches_played - Total matches (home + away)
points - Total points earned
goal_for - Goals scored by the team
goal_against - Goals conceded by the team
goal_diff - Goal difference (goals_for - goals_against)

Results must be ordered by: Points (desc) → Goal Difference (desc) → Team Name (asc)

Input & Output

example_1.sql — Basic League Example

$ Input: Teams table:\n+----------+-------------+\n| team_id | team_name |\n+----------+-------------+\n| 10 | Leetcode FC |\n| 20 | NewYork FC |\n| 30 | Atlanta FC |\n| 40 | Chicago FC |\n+----------+-------------+\n\nMatches table:\n+---------------+---------------+-------------------+-------------------+\n| home_team_id | away_team_id | home_team_goals | away_team_goals |\n+---------------+---------------+-------------------+-------------------+\n| 10 | 20 | 3 | 0 |\n| 30 | 10 | 2 | 2 |\n| 10 | 40 | 4 | 1 |\n| 20 | 30 | 1 | 5 |\n| 40 | 20 | 2 | 1 |\n+---------------+---------------+-------------------+-------------------+

› Output: +--------------+----------------+--------+-----------+--------------+-----------+\n| team_name | matches_played | points | goal_for | goal_against | goal_diff |\n+--------------+----------------+--------+-----------+--------------+-----------+\n| Leetcode FC | 3 | 7 | 9 | 3 | 6 |\n| Atlanta FC | 2 | 4 | 7 | 3 | 4 |\n| Chicago FC | 2 | 3 | 3 | 6 | -3 |\n| NewYork FC | 3 | 0 | 2 | 8 | -6 |\n+--------------+----------------+--------+-----------+--------------+-----------+

💡 Note: Leetcode FC played 3 matches (2 home, 1 away), won 2 games (6 points) and drew 1 (1 point) for 7 total points. They scored 9 goals (3+2+4) and conceded 3 (0+2+1). Atlanta FC had 1 win and 1 draw for 4 points. The teams are ordered by points descending, then goal difference descending.

example_2.sql — Tie-Breaking Example

$ Input: Teams table:\n+----------+-------------+\n| team_id | team_name |\n+----------+-------------+\n| 1 | Arsenal |\n| 2 | Chelsea |\n| 3 | Liverpool |\n+----------+-------------+\n\nMatches table:\n+---------------+---------------+-------------------+-------------------+\n| home_team_id | away_team_id | home_team_goals | away_team_goals |\n+---------------+---------------+-------------------+-------------------+\n| 1 | 2 | 2 | 1 |\n| 2 | 3 | 1 | 1 |\n| 3 | 1 | 0 | 3 |\n+---------------+---------------+-------------------+-------------------+

› Output: +-----------+----------------+--------+-----------+--------------+-----------+\n| team_name | matches_played | points | goal_for | goal_against | goal_diff |\n+-----------+----------------+--------+-----------+--------------+-----------+\n| Arsenal | 2 | 6 | 5 | 1 | 4 |\n| Chelsea | 2 | 1 | 2 | 3 | -1 |\n| Liverpool | 2 | 1 | 1 | 4 | -3 |\n+-----------+----------------+--------+-----------+--------------+-----------+

💡 Note: Arsenal wins both matches for 6 points. Chelsea and Liverpool both have 1 point, but Chelsea has better goal difference (-1 vs -3), so Chelsea ranks higher. This demonstrates the tie-breaking rules in action.

example_3.sql — Team With No Matches

$ Input: Teams table:\n+----------+-------------+\n| team_id | team_name |\n+----------+-------------+\n| 1 | Real Madrid |\n| 2 | Barcelona |\n| 3 | Atletico |\n+----------+-------------+\n\nMatches table:\n+---------------+---------------+-------------------+-------------------+\n| home_team_id | away_team_id | home_team_goals | away_team_goals |\n+---------------+---------------+-------------------+-------------------+\n| 1 | 2 | 1 | 3 |\n+---------------+---------------+-------------------+-------------------+

› Output: +--------------+----------------+--------+-----------+--------------+-----------+\n| team_name | matches_played | points | goal_for | goal_against | goal_diff |\n+--------------+----------------+--------+-----------+--------------+-----------+\n| Barcelona | 1 | 3 | 3 | 1 | 2 |\n| Atletico | 0 | 0 | 0 | 0 | 0 |\n| Real Madrid | 1 | 0 | 1 | 3 | -2 |\n+--------------+----------------+--------+-----------+--------------+-----------+

💡 Note: Atletico hasn't played any matches, so all their statistics are 0. Barcelona wins their match against Real Madrid. This shows how teams with no matches are handled (using COALESCE to default to 0) and the final alphabetical ordering tie-breaker.

Visualization

Tap to expand

Understanding the Visualization

Split Each Match

For every match, create two records - one for home team performance and one for away team performance

Calculate Points

Assign 3 points for wins, 1 point for draws, 0 points for losses based on goal comparison

Track Goals

Record goals scored (goal_for) and goals conceded (goal_against) for each team in each match

Combine with UNION

Merge all home team records with all away team records into one dataset

Aggregate by Team

Group by team_id and sum up matches_played, points, goal_for, and goal_against

Final Rankings

Sort by points (desc), goal difference (desc), then team name (asc) to get final standings

Key Takeaway

🎯 Key Insight: Treat each match as generating two separate team records (home and away), then aggregate them using GROUP BY. This single-pass approach with UNION ALL is far more efficient than multiple subqueries and results in clean, maintainable SQL code.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through matches table plus sorting

✓ Linear Growth

Space Complexity

O(n)

Space for CTE results and final aggregation

⚡ Linearithmic Space

Constraints

1 ≤ Number of teams ≤ 1000
0 ≤ Number of matches ≤ 10⁴
0 ≤ home_team_goals, away_team_goals ≤ 100
Each match has exactly two different teams
All team_id values in Matches table exist in Teams table

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses a single CTE with UNION ALL to combine home and away team statistics in one pass, then aggregates using GROUP BY. This approach is highly efficient with O(n) time complexity and avoids multiple table scans. The key insight is treating home and away games as separate records initially, then combining them through aggregation.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Subquery Approach	O(n²)	O(n)	Use separate subqueries for each statistic calculation
Single CTE with UNION ALL (Optimal)	O(n)	O(n)	Use CTE to process all statistics in one pass with UNION ALL

Multiple Subquery Approach — Algorithm Steps

Create subquery to count total matches played per team
Create subquery to calculate total points per team
Create subquery to calculate goals scored per team
Create subquery to calculate goals conceded per team
Join all subqueries with the Teams table
Calculate goal difference and apply ordering

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan 1: Count Matches

First pass through matches table to count games per team

Scan 2: Calculate Points

Second pass to calculate points based on win/draw/loss

Scan 3: Goals For

Third pass to sum goals scored by each team

Scan 4: Goals Against

Fourth pass to sum goals conceded by each team

Join Results

Join all subquery results together

Code -

solution.c — C

// SQL Problem - Multiple Subqueries (Brute Force)
char* sql = 
"SELECT t.team_name, "
"       COALESCE(m.matches, 0) as matches_played, "
"       COALESCE(p.points, 0) as points, "
"       COALESCE(g.goals_for, 0) as goal_for, "
"       COALESCE(g.goals_against, 0) as goal_against, "
"       COALESCE(g.goals_for, 0) - COALESCE(g.goals_against, 0) as goal_diff "
"FROM Teams t "
"LEFT JOIN ( "
"    SELECT team_id, COUNT(*) as matches "
"    FROM ( "
"        SELECT home_team_id as team_id FROM Matches "
"        UNION ALL "
"        SELECT away_team_id as team_id FROM Matches "
"    ) all_matches GROUP BY team_id "
") m ON t.team_id = m.team_id "
"LEFT JOIN ( "
"    SELECT team_id, SUM(pts) as points "
"    FROM ( "
"        SELECT home_team_id as team_id, "
"               CASE WHEN home_team_goals > away_team_goals THEN 3 "
"                    WHEN home_team_goals = away_team_goals THEN 1 "
"                    ELSE 0 END as pts "
"        FROM Matches "
"        UNION ALL "
"        SELECT away_team_id as team_id, "
"               CASE WHEN away_team_goals > home_team_goals THEN 3 "
"                    WHEN away_team_goals = home_team_goals THEN 1 "
"                    ELSE 0 END as pts "
"        FROM Matches "
"    ) team_points GROUP BY team_id "
") p ON t.team_id = p.team_id "
"ORDER BY points DESC, goal_diff DESC, team_name ASC";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the matches table for each statistic

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for multiple subquery results

⚡ Linearithmic Space

Constraints

1 ≤ Number of teams ≤ 1000
0 ≤ Number of matches ≤ 10⁴
0 ≤ home_team_goals, away_team_goals ≤ 100
Each match has exactly two different teams
All team_id values in Matches table exist in Teams table

42.3K Views

High Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Multiple Subquery Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler