-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL Code.sql
124 lines (115 loc) · 3.11 KB
/
SQL Code.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
USE ESPN;
-- Query to get all rows from result_table
SELECT * FROM result_table;
-- Query to get number of matches played, wins, and losses for each team between 2007 and 2011
-- Indian won the world cup in 2011
SELECT
Team_name,
COUNT(1) AS no_of_matches_played,
sum(win_flag) AS no_of_wins,
COUNT(1) - sum(win_flag) AS no_of_loss
FROM (
SELECT
Team1 AS Team_name,
CASE
WHEN Team1 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2007-04-29' AND '2011-02-18'
UNION ALL
SELECT
Team2 AS Team_name,
CASE
WHEN Team2 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2007-04-29' AND '2011-02-18'
) A
GROUP BY 1
ORDER BY 2 DESC;
-- Query to get number of matches played, wins, and losses for each team between 2011 and 2015
-- Australia won the world cup in 2015
SELECT
Team_name,
COUNT(1) AS no_of_matches_played,
sum(win_flag) AS no_of_wins,
COUNT(1) - sum(win_flag) AS no_of_loss
FROM (
SELECT
Team1 AS Team_name,
CASE
WHEN Team1 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2011-04-03' AND '2015-02-13'
UNION ALL
SELECT
Team2 AS Team_name,
CASE
WHEN Team2 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2011-04-03' AND '2015-02-13'
) A
GROUP BY 1
ORDER BY 2 DESC;
-- Query to get number of matches played, wins, and losses for each team between 2015 and 2019
-- England won the world cup in 2019
SELECT
Team_name,
COUNT(1) AS no_of_matches_played,
SUM(win_flag) AS no_of_wins,
COUNT(1) - SUM(win_flag) AS no_of_loss
FROM (
SELECT
Team1 AS Team_name,
CASE
WHEN Team1 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2015-03-30' AND '2019-05-29'
UNION ALL
SELECT
Team2 AS Team_name,
CASE
WHEN Team2 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2015-03-30' AND '2019-05-29'
) A
GROUP BY 1
ORDER BY 2 DESC;
-- Query to get number of matches played, wins, and losses for each team between 2019 and 2022
-- 2023 data will be colleted in September 2023 as in October 2023 the world cup will begin.
SELECT
Team_name,
COUNT(1) AS no_of_matches_played,
SUM(win_flag) AS no_of_wins,
COUNT(1) - SUM(win_flag) AS no_of_loss
FROM (
SELECT
Team1 AS Team_name,
CASE
WHEN Team1 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2019-05-15' AND '2022-12-31'
UNION ALL
SELECT
Team2 AS Team_name,
CASE
WHEN Team2 = Winner THEN 1
ELSE 0
END AS win_flag
FROM result_table
WHERE Match_Date BETWEEN '2019-05-15' AND '2022-12-31'
) A
GROUP BY 1
ORDER BY 2 DESC;