IPL Data Analysis SQL Interview Questions | Analytics Engineering

[@ankitbansal6]

Grab your seat here : www.namastesql.com/course-detail/analytics-engineering-bootcamp-duration-18-weeks

[@vinothkannaramsingh8224]

CREATE Table statement : create table cricket_match( matchid integer, ballnumber integer, inningno integer, overs float, outcome varchar(100), batter varchar(100), bowler varchar(100), score float )

[@saralavasudevan5167]

Hey Abkit great solve! This was my approach: with mycte as ( select matchid, batter as player, 'Batted' as type_of_play from cricket_match group by matchid,batter union select matchid, bowler as player, 'Bowled' as type_of_play from cricket_match group by matchid, bowler ) select player, count(distinct matchid) as total_matches, sum(case when type_of_play = 'Batted' then 1 else 0 end) as batting_matches, sum(case when type_of_play = 'Bowled' then 1 else 0 end) as bowling_matches from mycte group by player order by 2 desc

[@Datapassenger_prashant]

wow sir, you are the God of SQL when it comes to query optimization.. what an awesome solution in mentos life.. 🙌

[@prashastjaipurkar2952]

This is a very good question. My approach was similar to your normal life approach: with combine_bowler_batter as ( select match_id, bowler as player from cricket_match union select match_id, batter as player from cricket_match ) select a.player, count(distinct a.match_id) as total_matches_played, count(distinct b.match_id) as batting_matches, count(distinct c.match_id) as bowling_matches from combine_bowler_batter a left join cricket_match b on a.player = b.batter left join cricket_match c on a.player = c.bowler group by player

[@dasoumya]

Hi Ankit! Here is my approach: with cte as(select match_id,batter as player, 'batter' as playing_mode from cricket_match union all select match_id,bowler, 'bowler' as playing_mode from cricket_match) select player, count(distinct match_id) as total_match_played, count(distinct case when playing_mode='batter' then match_id end) as batting_matches, count(distinct case when playing_mode='bowler' then match_id end) as bowling_matches from cte group by player;

[@007SAMRATROY]

This one's my approach too and its cool.

[@SnapMathShorts]

My Approach : with cte as ( select battler as player,matchid, 'battler' as player_type from cricket_match union select bowler as player,matchid,'bowler' as player_type from cricket_match) select player, count(distinct matchid) as matches_played , count(case when player_type ='battler' then matchid end) as batting_matches, count( case when player_type ='bowler' then matchid end) as bowler_matches from cte group by 1 order by 1;

[@seetharamgoutham129]

Wonderful Explanation Sir.

[@mantisbrains]

select player, total_matches_played, coalesce(batting_matches,0), coalesce(bowling_matches,0) from (select player, count(match_id) as total_matches_played from (select match_id, batsman as player from cricket_match union select match_id, bowler as player from cricket_match)a group by player)a left join (select batsman , count(distinct match_id) as batting_matches from cricket_match group by batsman)b on a.player = b.batsman left join (select bowler , count(distinct match_id) as bowling_matches from cricket_match group by bowler)p on b.batsman = p. bowler I did the normal approach way but gave inner join insted of left join and did distinct count instead of count while doing union as i forgot union will itself make it distinct, forgot about coalesce also to fill nulls after left join. Thanks, Ankit !

[@jawakar8266]

Thank you for sharing great mentos life solution!

[@shubhamgoyal5227]

hello ankit sir, It was really nice question Here is my solution using case when with cte as ( select matchid, batter Player_name, 'Batter' as played_as from cricket_match union select matchid, bowler, 'Bowler' as played_as from cricket_match) SELECT Player_name, count(distinct matchid) matches_played, count(case when played_as ='Batter' then played_as else null end)Played_as_batter, count(case when played_as ='Bowler' then played_as else null end)Played_as_Bowler from cte group by Player_name

[@tenzinchoepheldev]

Thanks 🙏

[@pramodmittal9761]

I was asked this question recently in NEC interview

[@pruthivinayak157]

Hello Ankit, could you please share the top 5 partnerships made by any two batsmen from this dataset

[@suchitraroutray4705]

Hi sir, Please help me with the 1st ques Recently asked in a interview 1.table employee Column empid, joining date, salary, department Find employees who are eligible for gratuity as of now till today(eligibility criteria is person should have worked for more than 5year) 2.table employee Column emp id, swipe You have data for a single employee with swipe data as below in timestamp .he logged in 9 am and loggedout 3pm with in that he has taken breaks find the time he was there in workspace Emp id swipe 123 09/09/2024 09:00 am 123 09/09/2024 11:00 am 123 09/09/2024 13:00pm 123 09/09/2024 15:00Pm

[@rajmathichithramani]

HI Ankit ...i have doubt... To delete commands after using triggers....the records are deleting in Original table.... But in virtual table that deleted records are available na ....those records are consuming memory which is in virtual table??....Please help on this...

[@KiranmayiNaraharisetti]

My Approach: with batted_matches as (select distinct batter as player,count(DISTINCT match_id) as matches_batted from cricket group by batter) ,bowled_matches as (select distinct bowler as player,count(DISTINCT match_id) as matches_bowled from cricket group by bowler) ,total_matches as ( select player,COUNT(match_id) as total_matches from (select batter as player, match_id from cricket UNION select bowler as player, match_id from cricket) a group by player ) select COALESCE(ba.player,bo.player) as player,t.total_matches,COALESCE(matches_batted,0) as matches_batted, COALESCE(matches_bowled,0) as matches_bowled from batted_matches ba full outer join bowled_matches bo on ba.player = bo.player join total_matches t on t.player = ba.player or t.player = bo.player

[@rahulkanojiya6256]

One more mentos solution with all_players as ( select match_id, bowler as player_name , 1 as isbowler , 0 as isbatsman from cricket_match union select match_id, batsman as player_name, 0 as isbowler , 1 as isbatsman from cricket_match) select player_name,count(distinct match_id) as total_matches, sum(isbowler) as bowling_matches, sum(isbatsman) as batting_matches from all_players group by player_name

[@Mathematica1729]

Great

[@007SAMRATROY]

Kindly attach the create table script also.

[@ASH_ISLIVE]

--create table cricket_match --( --matchid int, --ballnumber int, --inningno int, --overs decimal(9,3), --outcome varchar(25), --batter varchar(30), --bowler varchar(30), --score int --);

[@nd9267]

;with cte as ( select matchid ,batter player from cricket_match cm union select matchid ,bowler from cricket_match cm ) ,cte2 as ( select player ,count(distinct matchid) total_matches_played from cte group by player ) select c.*, count(distinct cm.matchid) battling_matches, count(distinct cm2.matchid) bowling_matches from cte2 c left join cricket_match cm on c.player = cm.batter left join cricket_match cm2 on c.player = cm2.bowler group by c.player ,c.total_matches_played order by player

[@nithinanthony7547]

Hi ankit namaste sel server issue Please recitify

[@ankitbansal6]

It's fine now

[@kal_x9178]

Striker ❌ batter ✅😅😅

[@kal_x9178]

I should go with normal life

[@arjundev4908]

WITH CTE AS(SELECT DISTINCT MATCHID,BATTER AS PLAYER FROM cricket_match UNION SELECT DISTINCT MATCHID,BOWLER AS PLAYER FROM cricket_match),V1 AS( SELECT PLAYER,COUNT(DISTINCT MATCHID) AS MATCHES_PLAYED from cte as C group by 1),BAT AS( SELECT V1.*,COUNT(DISTINCT MATCHID) AS BATTING_MATCHES FROM V1 LEFT JOIN cricket_match AS C ON V1.PLAYER = C.BATTER group by 1) SELECT B.*,COUNT(DISTINCT MATCHID) AS BOWLING_MATCHES FROM BAT AS B LEFT JOIN cricket_match AS C ON B.PLAYER = C.BOWLER group by 1;