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
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
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;
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 !
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
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;
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...
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
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
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
;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
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;
Grab your seat here :
www.namastesql.com/course-detail/analytics-engineering-bootcamp-duration-18-weeks
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
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
)
wow sir, you are the God of SQL when it comes to query optimization.. what an awesome solution in mentos life.. 🙌
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
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;
This one's my approach too and its cool.
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 !
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
Thank you for sharing great mentos life solution!
Wonderful Explanation Sir.
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;
I was asked this question recently in NEC interview
Hello Ankit, could you please share the top 5 partnerships made by any two batsmen from this dataset
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...
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
Great
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
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
Kindly attach the create table script also.
--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
--);
;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
Hi ankit
namaste sel server issue
Please recitify
It's fine now
Striker ❌ batter ✅😅😅
I should go with normal life
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;