IPL Data Analysis SQL Interview Questions | Analytics Engineering

Поділитися
Вставка
  • Опубліковано 22 гру 2024

КОМЕНТАРІ •

  • @ankitbansal6
    @ankitbansal6  Місяць тому

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

  • @saralavasudevan5167
    @saralavasudevan5167 Місяць тому +2

    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

  • @vinothkannaramsingh8224
    @vinothkannaramsingh8224 9 днів тому +5

    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
    )

  • @Datapassenger_prashant
    @Datapassenger_prashant Місяць тому +1

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

  • @prashastjaipurkar2952
    @prashastjaipurkar2952 18 днів тому

    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
    @dasoumya Місяць тому +1

    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
      @007SAMRATROY Місяць тому +1

      This one's my approach too and its cool.

  • @mantisbrains
    @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 !

  • @shubhamgoyal5227
    @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

  • @jawakar8266
    @jawakar8266 Місяць тому

    Thank you for sharing great mentos life solution!

  • @seetharamgoutham129
    @seetharamgoutham129 Місяць тому

    Wonderful Explanation Sir.

  • @SnapMathShorts
    @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;

  • @pramodmittal9761
    @pramodmittal9761 Місяць тому

    I was asked this question recently in NEC interview

  • @pruthivinayak157
    @pruthivinayak157 Місяць тому

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

  • @rajmathichithramani
    @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...

  • @rahulkanojiya6256
    @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

  • @KiranmayiNaraharisetti
    @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

  • @suchitraroutray4705
    @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

  • @007SAMRATROY
    @007SAMRATROY Місяць тому +1

    Kindly attach the create table script also.

    • @ASH_ISLIVE
      @ASH_ISLIVE Місяць тому +1

      --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
    @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
    @nithinanthony7547 Місяць тому

    Hi ankit
    namaste sel server issue
    Please recitify

  • @kal_x9178
    @kal_x9178 Місяць тому +2

    Striker ❌ batter ✅😅😅

  • @kal_x9178
    @kal_x9178 Місяць тому

    I should go with normal life

  • @arjundev4908
    @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;