1SELECT
2  'Benfica' AS team,
3  SUM(
4    CASE
5      WHEN result = 'W' THEN 1
6      ELSE 0
7    END
8  ) AS wins,
9  SUM(
10    CASE
11      WHEN result = 'D' THEN 1
12      ELSE 0
13    END
14  ) AS draws,
15  SUM(
16    CASE
17      WHEN result = 'L' THEN 1
18      ELSE 0
19    END
20  ) AS losses,
21  SUM(benfica_goals) AS goals_for,
22  SUM(opponent_goals) AS goals_against,
23  ROUND(SUM(benfica_goals) / COUNT(*), 2) AS goals_per_match
24FROM
25  (
26    SELECT
27      CASE
28        WHEN m.home_team_name = 'Benfica'
29        AND m.home_score > m.away_score THEN 'W'
30        WHEN m.away_team_name = 'Benfica'
31        AND m.away_score > m.home_score THEN 'W'
32        WHEN m.home_score = m.away_score THEN 'D'
33        ELSE 'L'
34      END AS result,
35      CASE
36        WHEN m.home_team_name = 'Benfica' THEN m.home_score
37        ELSE m.away_score
38      END AS benfica_goals,
39      CASE
40        WHEN m.home_team_name = 'Benfica' THEN m.away_score
41        ELSE m.home_score
42      END AS opponent_goals
43    FROM
44      "@blt.ultimate_soccer_dataset.matches" m
45    WHERE
46      (
47        m.home_team_name = 'Benfica'
48        OR m.away_team_name = 'Benfica'
49      )
50      AND m.status = 'Match Finished'
51    ORDER BY
52      m.kickoff_timestamp DESC
53    LIMIT
54      10
55  )
56UNION ALL
57SELECT
58  'Real Madrid' AS team,
59  SUM(
60    CASE
61      WHEN result = 'W' THEN 1
62      ELSE 0
63    END
64  ) AS wins,
65  SUM(
66    CASE
67      WHEN result = 'D' THEN 1
68      ELSE 0
69    END
70  ) AS draws,
71  SUM(
72    CASE
73      WHEN result = 'L' THEN 1
74      ELSE 0
75    END
76  ) AS losses,
77  SUM(madrid_goals) AS goals_for,
78  SUM(opponent_goals) AS goals_against,
79  ROUND(SUM(madrid_goals) / COUNT(*), 2) AS goals_per_match
80FROM
81  (
82    SELECT
83      CASE
84        WHEN m.home_team_name = 'Real Madrid'
85        AND m.home_score > m.away_score THEN 'W'
86        WHEN m.away_team_name = 'Real Madrid'
87        AND m.away_score > m.home_score THEN 'W'
88        WHEN m.home_score = m.away_score THEN 'D'
89        ELSE 'L'
90      END AS result,
91      CASE
92        WHEN m.home_team_name = 'Real Madrid' THEN m.home_score
93        ELSE m.away_score
94      END AS madrid_goals,
95      CASE
96        WHEN m.home_team_name = 'Real Madrid' THEN m.away_score
97        ELSE m.home_score
98      END AS opponent_goals
99    FROM
100      "@blt.ultimate_soccer_dataset.matches" m
101    WHERE
102      (
103        m.home_team_name = 'Real Madrid'
104        OR m.away_team_name = 'Real Madrid'
105      )
106      AND m.status = 'Match Finished'
107    ORDER BY
108      m.kickoff_timestamp DESC
109    LIMIT
110      10
111  )