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
Understanding the Visualization
1
Input
Teams and Matches tables
2
UNION
Combine host/guest perspectives
3
Output
Team points ranked
Key Takeaway
🎯 Key Insight: Use UNION to treat each team's host and guest matches uniformly, then GROUP BY to aggregate total points
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code