IPL Analysis using SQL------
SELECT * FROM ipl_database.matches;
use ipl_database
-- WHAT ARE THE TOP 5 PLAYERS WITH THE MOST PLAYER OF THE MATCH AWARDS?
SELECT
player_of_match, COUNT(*) AS awards_count
FROM
matches
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5
-- HOW MANY MATCHES WERE WON BY EACH TEAM IN EACH SEASON?
SELECT
season, winner, COUNT(*) AS matches_won
FROM
matches
GROUP BY 1 , 2
-- WHAT IS THE AVERAGE STRIKE RATE OF BATSMEN IN THE IPL DATASET?
SELECT
AVG(strike_rate) AS average_strike_rate
FROM
(SELECT
batsman, SUM(total_runs) / COUNT(ball) * 100 AS strike_Rate
FROM
deliveries
GROUP BY batsman) AS batsman_stats;
-- WHAT IS THE NUMBER OF MATCHES WON BY EACH TEAM BATTING FIRST VERSUS BATTING
SECOND?
SELECT
batting_first, COUNT(*) AS matches_won
FROM
(SELECT
CASE
WHEN win_by_runs > 0 THEN team1
ELSE team2
END AS batting_first
FROM
matches
WHERE
winner <> 'Tie') AS batting_first_teams
GROUP BY 1
-- WHICH BATSMAN HAS THE HIGHEST STRIKE RATE (MINIMUM 200 RUNS SCORED)?
SELECT
batsman, SUM(total_runs) / COUNT(ball) * 100 AS strike_Rate
FROM
deliveries
GROUP BY batsman
HAVING SUM(total_runs) >= 200
ORDER BY strike_Rate desc
LIMIT 1;
-- HOW MANY TIMES HAS EACH BATSMAN BEEN DISMISSED BY THE BOWLER 'MALINGA'?
SELECT
batsman, COUNT(*) AS total_dissmisal
FROM
deliveries
WHERE
bowler LIKE 'SL MALINGA'
AND player_dismissed like '% %'
GROUP BY 1
-- WHAT IS THE AVERAGE PERCENTAGE OF BOUNDARIES (FOURS AND SIXES COMBINED) HIT BY EACH BATSMAN?
SELECT
batsman,
AVG(CASE
WHEN batsman_runs = 4 OR batsman_runs = 6 THEN 1
ELSE 0
END) * 100 AS avg_perc_boundary
FROM
deliveries
GROUP BY batsman
-- WHAT IS THE AVERAGE NUMBER OF BOUNDARIES HIT BY EACH TEAM IN EACH SEASON?
SELECT
season, batting_team, AVG(fours + sixes) AS avg_boundaries
FROM
(SELECT
season,
match_id,
batting_team,
SUM(CASE
WHEN batsman_runs = 4 THEN 1
ELSE 0
END) AS fours,
SUM(CASE
WHEN batsman_runs = 6 THEN 1
ELSE 0
END) AS sixes
FROM
deliveries, matches
WHERE
deliveries.match_id = [Link]
GROUP BY season , match_id , batting_team) AS team_boundaries
GROUP BY season , batting_team;
-- WHAT IS THE HIGHEST PARTNERSHIP (RUNS) FOR EACH TEAM IN EACH SEASON?
select season, batting_team, max(total_runs) as highest_partnership
from( select season, batting_team, partnership, sum(total_runs) as total_runs
from( select season, match_id, batting_team, over_no, sum(batsman_runs) as partnership,
sum(batsman_runs) + sum(extra_runs) as total_runs
from deliveries, matches
where deliveries.match_id = [Link]
group by season, match_id, batting_team, over_no) as team_scores
group by season, batting_team, partnership) as highest_partnership
group by season, batting_team;
-- HOW MANY EXTRAS (WIDES & NO-BALLS) WERE BOWLED BY EACH TEAM IN EACH MATCH?
select [Link] as match_no, d.bowling_team, sum(d.extra_runs) as extras
from matches m
join deliveries d
on [Link] = d.match_id
where extra_runs > 0
group by 1,2;
-- WHICH BOWLER HAS THE BEST BOWLING FIGURES (MOST WICKETS TAKEN) IN A SINGLE MATCH?
select [Link] as match_no, bowler, count(*) as wickets_taken
from matches m
join deliveries d
on [Link] = d.match_id
where d.player_dismissed like '% %'
group by 1,2
order by 3 desc
limit 1
-- HOW MANY MATCHES RESULTED IN A WIN FOR EACH TEAM IN EACH CITY?
select [Link],
case when m.team1 = [Link] then m.team1
when m.team2=[Link] then m.team2
else 'draw' end as winning_team, count(*) as wins
from matches m
join deliveries d
on [Link]=d.match_id
where [Link] <> 'Tie'
group by [Link] , winning_team
-- HOW MANY TIMES DID EACH TEAM WIN THE TOSS IN EACH SEASON?
select season, toss_winner, count(*) as toss_wins
from matches
group by 1,2
-- HOW MANY MATCHES DID EACH PLAYER WIN THE "PLAYER OF THE MATCH" AWARD?
select player_of_match, count(*) as total_wins
from matches
where player_of_match like '% %'
group by 1
order by 2 desc
-- WHAT IS THE AVERAGE NUMBER OF RUNS SCORED IN EACH OVER OF THE INNINGS IN EACH MATCH?
select [Link], [Link], d.over_no,
avg(d.total_runs) as avg_runs_per_over
from matches m
join deliveries d
on [Link]=d.match_id
group by 1,2,3
-- WHICH TEAM HAS THE HIGHEST TOTAL SCORE IN A SINGLE MATCH?
select [Link], [Link] as match_no, d.batting_team,
sum(d.total_runs) as total_score from matches m
join deliveries d
on [Link]=d.match_id
group by 1,2,3
order by 4 desc
limit 1
-- WHICH BATSMAN HAS SCORED THE MOST RUNS IN A SINGLE MATCH?
select [Link] as match_no, [Link], sum(d.total_runs) as most_runs
from matches m
join deliveries d
on [Link]=d.match_id
group by 1,2
order by 3 desc
limit 1