Team Scores in Football Tournament - Problem

You have two tables representing a football tournament: Teams and Matches.

The Teams table contains team information with columns team_id (unique identifier) and team_name.

The Matches table records finished matches with columns: match_id (unique identifier), host_team and guest_team (team IDs), and host_goals and guest_goals (goals scored).

Scoring Rules:

  • Win: 3 points (scored more goals than opponent)
  • Draw: 1 point (scored same number of goals as opponent)
  • Loss: 0 points (scored fewer goals than opponent)

Write a SQL query to calculate the total points for each team. Return team_id, team_name, and num_points ordered by num_points descending, then by team_id ascending.

Table Schema

Teams
Column Name Type Description
team_id PK int Unique team identifier
team_name varchar Name of the team
Primary Key: team_id
Matches
Column Name Type Description
match_id PK int Unique match identifier
host_team int ID of the host team
guest_team int ID of the guest team
host_goals int Goals scored by host team
guest_goals int Goals scored by guest team
Primary Key: match_id

Input & Output

Example 1 — Basic Tournament Scoring
Input Tables:
Teams
team_id team_name
10 Leetcode FC
20 New York FC
30 Atlanta FC
40 Chicago FC
50 Toronto FC
Matches
match_id host_team guest_team host_goals guest_goals
1 10 20 3 0
2 30 10 2 2
3 10 50 5 1
4 20 30 1 0
5 50 30 1 0
Output:
team_id team_name num_points
10 Leetcode FC 7
20 New York FC 3
50 Toronto FC 3
30 Atlanta FC 1
40 Chicago FC 0
💡 Note:

Points calculation:

  • Leetcode FC (10): Win vs 20 (3pts) + Draw vs 30 (1pt) + Win vs 50 (3pts) = 7pts
  • New York FC (20): Loss vs 10 (0pts) + Win vs 30 (3pts) = 3pts
  • Toronto FC (50): Loss vs 10 (0pts) + Win vs 30 (3pts) = 3pts
  • Atlanta FC (30): Draw vs 10 (1pt) + Loss vs 20 (0pts) + Loss vs 50 (0pts) = 1pt
  • Chicago FC (40): No matches = 0pts
Example 2 — All Draws Tournament
Input Tables:
Teams
team_id team_name
1 Team A
2 Team B
Matches
match_id host_team guest_team host_goals guest_goals
1 1 2 1 1
2 2 1 2 2
Output:
team_id team_name num_points
1 Team A 2
2 Team B 2
💡 Note:

Both teams drew both matches (1-1 and 2-2), earning 1 point each match for a total of 2 points each. Since points are tied, teams are ordered by team_id ascending.

Constraints

  • 1 ≤ team_id ≤ 100
  • 1 ≤ match_id ≤ 1000
  • host_goals, guest_goals ≥ 0
  • host_team ≠ guest_team
  • Each team plays 0 or more matches

Visualization

Tap to expand
Team Scores in Football Tournament INPUT Matches Table match_id host guest score 1 10 20 3-0 2 30 10 2-2 3 10 50 5-1 4 20 30 1-0 Teams Table team_id team_name 10 Leetcode FC 20 NewYork FC 30 Atlanta FC 50 Toronto FC Win=3pts | Draw=1pt | Loss=0pts Sort: points DESC, team_id ASC ALGORITHM STEPS 1 Join Teams with Matches LEFT JOIN on team as host and team as guest 2 Calculate Points per Match CASE WHEN for each team: host_goals > guest = 3 pts 3 Aggregate by Team GROUP BY team_id, team_name SUM all match points 4 Sort Results ORDER BY num_points DESC, team_id ASC Example: Team 10 Match 1: Host 3-0 = WIN (+3) Match 2: Guest 2-2 = DRAW (+1) Match 3: Host 5-1 = WIN (+3) Total: 7 points FINAL RESULT Output Table team_id team_name num_pts 10 Leetcode FC 7 20 NewYork FC 3 30 Atlanta FC 1 50 Toronto FC 0 Champion: Leetcode FC 7 points (2 wins, 1 draw) Verification Team 10: 3+1+3 = 7 pts - OK Team 20: 0+3 = 3 pts - OK Sorted correctly - OK Key Insight: Use UNION ALL to combine host and guest perspectives, then apply CASE WHEN for point calculation. Each team appears in matches as both host and guest, so we need to evaluate both scenarios. COALESCE handles teams with no matches (0 points). Final sort ensures consistent ordering. TutorialsPoint - Team Scores in Football Tournament | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Facebook 8
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