Team Scores in Football Tournament

Team Scores in Football Tournament

What is this problem about?

The Team Scores in Football Tournament interview question is a SQL problem involving the calculation of tournament standings. You are given a Teams table and a Matches table. Each match has a home team, an away team, and their respective goals. Points are awarded: 3 for a win, 1 for a draw, and 0 for a loss. Your goal is to return a table with the team name and their total points, sorted by points (descending) and then team name (ascending).

Why is this asked in interviews?

Wayfair and Amazon use this question to evaluate a candidate's proficiency with SQL joins, CASE statements, and aggregations. It’s a classic "data transformation" task where you need to look at each match from two perspectives (the home team's and the away team's) and then combine those results. It tests your ability to handle teams that might not have played any matches (returning 0 points for them).

Algorithmic pattern used

The primary pattern is the Database Aggregation and Union/Join.

1. Use a LEFT JOIN starting from the Teams table to ensure teams with no matches are included.
2. Within the join (or using a subquery/CTE), use a CASE statement to calculate points for each match:
   • For home teams: SUM(CASE WHEN home_goals > away_goals THEN 3 WHEN home_goals = away_goals THEN 1 ELSE 0 END).
   • Do the same for away teams.
3. Group by team_id and team_name to get the final totals.

Example explanation

Teams: [1: Lions, 2: Tigers]. Match: Lions 2 - 1 Tigers.

• Lions (Home): 2 > 1. Points = 3.
• Tigers (Away): 1 < 2. Points = 0. Teams: [3: Bears]. No matches. Result:
• Lions: 3
• Bears: 0
• Tigers: 0 Bears appear before Tigers because of alphabetical sorting on name when points are tied.

Common mistakes candidates make

The most common mistake is using an INNER JOIN, which excludes teams that haven't played any matches. Another error is only counting points for home matches or only for away matches, instead of summing both. Failing to handle NULL values (which should be treated as 0 points) is also a frequent oversight.

Interview preparation tip

When preparing for the Team Scores in Football Tournament coding problem, practice using COALESCE(sum_points, 0) to handle nulls from left joins. Also, understand the Database interview pattern for handling bidirectional relationships (home vs. away) in SQL. Using a CTE to first calculate points per match and then summing them is a clean, readable approach.