Team Scores in Football Tournament - Problem

⚽ Football Tournament Score Calculator

You're tasked with creating a tournament leaderboard system for a football championship! Given two database tables containing team information and match results, calculate the final standings based on standard football scoring rules.

📊 Database Schema

Teams Table:

Column NameTypeDescription
team_idintUnique team identifier
team_namevarcharName of the football team

Matches Table:

Column NameTypeDescription
match_idintUnique match identifier
host_teamintHome team ID
guest_teamintAway team ID
host_goalsintGoals scored by home team
guest_goalsintGoals scored by away team

🏆 Scoring System

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

📋 Requirements

Return a result table with columns: team_id, team_name, and num_points

Ordering: Sort by num_points (descending), then by team_id (ascending) for ties

Input & Output

example_1.sql — Basic Tournament
$ Input: Teams: [(10,'Leetcode FC'),(20,'New York FC'),(30,'Atlanta FC'),(40,'Chicago FC'),(50,'Toronto FC')] Matches: [(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: [(10,'Leetcode FC',7),(20,'New York FC',3),(50,'Toronto FC',3),(30,'Atlanta FC',1),(40,'Chicago FC',0)]
💡 Note: Leetcode FC: won vs NY FC (3 pts) + drew vs Atlanta (1 pt) + won vs Toronto (3 pts) = 7 pts. NY FC: lost to Leetcode (0) + won vs Atlanta (3) = 3 pts. Toronto FC: lost to Leetcode (0) + won vs Atlanta (3) = 3 pts. Atlanta FC: drew with Leetcode (1) + lost to NY FC (0) + lost to Toronto (0) = 1 pt. Chicago FC didn't play = 0 pts.
example_2.sql — All Draws
$ Input: Teams: [(1,'Team A'),(2,'Team B')] Matches: [(1,1,2,1,1),(2,2,1,2,2)]
› Output: [(1,'Team A',2),(2,'Team B',2)]
💡 Note: Both teams drew both matches, earning 1 point each per match. Total: 2 points each. Since points are tied, ordered by team_id ascending.
example_3.sql — No Matches Edge Case
$ Input: Teams: [(1,'Solo Team')] Matches: []
› Output: [(1,'Solo Team',0)]
💡 Note: Team didn't play any matches, so they have 0 points. The query should handle teams with no match records gracefully using LEFT JOIN.

Visualization

Tap to expand
🏆 Football Tournament Scoring VisualizationTeam ATeam B2-1Match ResultUNION ALL TransformationTeam A: 2 goals vs 1 opponent → Win → 3 pointsTeam B: 1 goal vs 2 opponent → Loss → 0 points✅ Normalized team-centric viewScoring Rules ApplicationWin: 3 Pointsgoals > opponent⚽⚽ vs ⚽Draw: 1 Pointgoals = opponent⚽ vs ⚽Loss: 0 Pointsgoals < opponent⚽ vs ⚽⚽Final Leaderboard (ORDER BY points DESC, team_id ASC)🥇 Team A7 points🥈 Team B3 points🥉 Team C1 point
Understanding the Visualization
1
Collect Match Results
Each match generates results for both home and away teams
2
Normalize Data
UNION ALL creates uniform records: team vs opponent
3
Apply Scoring Rules
Win=3pts, Draw=1pt, Loss=0pts using CASE statement
4
Aggregate Points
GROUP BY team and SUM all their match points
5
Create Leaderboard
ORDER BY points DESC, then team_id ASC for ties
Key Takeaway
🎯 Key Insight: UNION ALL transforms asymmetric match data into a uniform team-centric view, enabling efficient single-pass aggregation with optimal performance O(n)

Time & Space Complexity

Time Complexity
⏱️
O(n)

Single pass through matches table with efficient grouping

n
2n
✓ Linear Growth
Space Complexity
O(n)

Space for storing aggregated results and temporary UNION data

n
2n
⚡ Linearithmic Space

Related Problems

Constraints

  • 1 ≤ number of teams ≤ 100
  • 0 ≤ number of matches ≤ 103
  • 0 ≤ goals ≤ 100 per match
  • Each match involves exactly two different teams
  • team_id values are unique and positive integers
Asked in
Google 42 Amazon 38 Meta 29 Microsoft 25
42.3K Views
Medium Frequency
~15 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