League Statistics - Problem

You have two tables: Teams and Matches. Write a SQL query to report comprehensive league statistics for each team.

Teams table:

  • team_id (int): Unique team identifier
  • team_name (varchar): Team name

Matches table:

  • home_team_id (int): Home team identifier
  • away_team_id (int): Away team identifier
  • home_team_goals (int): Goals scored by home team
  • away_team_goals (int): Goals scored by away team

Scoring rules:

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

Return statistics including team name, matches played, points, goals for/against, and goal difference. Order by points (descending), then goal difference (descending), then team name (ascending).

Table Schema

Teams
Column Name Type Description
team_id PK int Unique identifier for each team
team_name varchar Name of the team
Primary Key: team_id
Matches
Column Name Type Description
home_team_id PK int ID of the home team
away_team_id PK int ID of the away team
home_team_goals int Goals scored by home team
away_team_goals int Goals scored by away team
Primary Key: home_team_id, away_team_id

Input & Output

Example 1 — Basic League Statistics
Input Tables:
Teams
team_id team_name
1 Team A
2 Team B
3 Team C
Matches
home_team_id away_team_id home_team_goals away_team_goals
1 2 3 1
2 3 1 1
3 1 0 2
Output:
team_name matches_played points goal_for goal_against goal_diff
Team A 2 6 5 1 4
Team B 2 1 2 4 -2
Team C 2 1 1 3 -2
💡 Note:

Team A wins both matches (3-1 vs Team B home, 2-0 vs Team C away) for 6 points. Team B draws with Team C and loses to Team A for 1 point. Team C draws with Team B and loses to Team A for 1 point. Teams B and C tie on points and goal difference, so ordered alphabetically.
Example 2 — Teams with Draws Only
Input Tables:
Teams
team_id team_name
1 Alpha
2 Beta
Matches
home_team_id away_team_id home_team_goals away_team_goals
1 2 2 2
Output:
team_name matches_played points goal_for goal_against goal_diff
Alpha 1 1 2 2 0
Beta 1 1 2 2 0
💡 Note:

Single match ends in 2-2 draw. Both teams get 1 point each, same goal difference (0), so ordered alphabetically by team name.

Constraints

  • 1 ≤ team_id ≤ 1000
  • 1 ≤ team_name.length ≤ 50
  • 0 ≤ home_team_goals, away_team_goals ≤ 20
  • Each match has different home_team_id and away_team_id

Visualization

Tap to expand
League Statistics Problem OverviewInput: Match Resultshomeawayh_goalsa_goalsAB31BC11CA02SQL Processing1. UNION ALLHome + Away views2. GROUP BY team3. Calculate pointsOutput: League TableteammatchespointsdiffA26+4B21-2C21-2Win = 3 points, Draw = 1 point, Loss = 0 points
Understanding the Visualization
1
Input
Teams and Matches tables
2
UNION ALL
Combine home/away perspectives
3
GROUP BY
Aggregate team statistics
4
Output
Ordered league table
Key Takeaway
🎯 Key Insight: UNION ALL is perfect for problems where entities play dual roles (home/away teams)

Related Problems

Asked in
Amazon 28 Facebook 15 Google 12
23.4K Views
Medium Frequency
~18 min Avg. Time
892 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