Leetcode 1841. League Statistics
Problem Explanation
In this problem, we are given information about football teams and their performance in various matches. We need to generate a statistics table containing information about the teams' performance, such as the number of matches played, points earned, goals scored, goals conceded, and goal difference.
We have two tables:
Teams
: This table contains the team_id and the respective team_name.Matches
: This table contains information about the matches, such as the home_team_id, away_team_id, home_team_goals, and away_team_goals.
Our goal is to write an SQL query to generate a statistics table with team_name, matches_played, points, goal_for, goal_against, and goal_diff columns.
Let's walk through an example:
Example
Teams:
team_id | team_name |
---|---|
1 | Ajax |
4 | Dortmund |
6 | Arsenal |
Matches:
home_team_id | away_team_id | home_team_goals | away_team_goals |
---|---|---|---|
1 | 4 | 0 | 1 |
1 | 6 | 3 | 3 |
4 | 1 | 5 | 2 |
6 | 1 | 0 | 0 |
Result:
team_name | matches_played | points | goal_for | goal_against | goal_diff |
---|---|---|---|---|---|
Dortmund | 2 | 6 | 6 | 2 | 4 |
Arsenal | 2 | 2 | 3 | 3 | 0 |
Ajax | 4 | 2 | 5 | 9 | -4 |
Approach
To generate the required statistics table, we can use the following approach:
- Calculate matches played, goals scored, and goals conceded for each team in the home and away matches.
- Combine the home and away statistics for each team.
- Calculate total points and goal difference for each team.
- Order the teams by total points, goal difference, and team_name.
Solution
SQL
1WITH home_stats AS (
2 SELECT
3 home_team_id AS team_id,
4 COUNT(*) AS matches_played,
5 SUM(home_team_goals) AS goal_for,
6 SUM(away_team_goals) AS goal_against,
7 SUM(CASE
8 WHEN home_team_goals > away_team_goals THEN 3
9 WHEN home_team_goals = away_team_goals THEN 1
10 ELSE 0
11 END) AS points
12 FROM
13 Matches
14 GROUP BY
15 home_team_id
16),
17away_stats AS (
18 SELECT
19 away_team_id AS team_id,
20 COUNT(*) AS matches_played,
21 SUM(away_team_goals) AS goal_for,
22 SUM(home_team_goals) AS goal_against,
23 SUM(CASE
24 WHEN away_team_goals > home_team_goals THEN 3
25 WHEN away_team_goals = home_team_goals THEN 1
26 ELSE 0
27 END) AS points
28 FROM
29 Matches
30 GROUP BY
31 away_team_id
32),
33combined_stats AS (
34 SELECT
35 T.team_id,
36 T.team_name,
37 COALESCE(H.matches_played, 0) + COALESCE(A.matches_played, 0) AS matches_played,
38 COALESCE(H.goal_for, 0) + COALESCE(A.goal_for, 0) AS goal_for,
39 COALESCE(H.goal_against, 0) + COALESCE(A.goal_against, 0) AS goal_against,
40 COALESCE(H.points, 0) + COALESCE(A.points, 0) AS points
41 FROM
42 Teams T
43 LEFT JOIN home_stats H ON T.team_id = H.team_id
44 LEFT JOIN away_stats A ON T.team_id = A.team_id
45)
46SELECT
47 team_name,
48 matches_played,
49 points,
50 goal_for,
51 goal_against,
52 goal_for - goal_against AS goal_diff
53FROM
54 combined_stats
55ORDER BY
56 points DESC,
57 goal_diff DESC,
58 team_name ASC;
Got a question?ย Ask the Teaching Assistantย anything you don't understand.
Still not clear? Ask in the Forum, ย Discordย orย Submitย the part you don't understand to our editors.