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:

1. Teams: This table contains the team_id and the respective team_name.
2. 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:

Matches:

Result:

Approach

To generate the required statistics table, we can use the following approach:

1. Calculate matches played, goals scored, and goals conceded for each team in the home and away matches.
2. Combine the home and away statistics for each team.
3. Calculate total points and goal difference for each team.
4. 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;