SQL Interview Question - Solution (Part - XI) | #sql #data engineers #interview

#education #sql #sqlinterview #dataengineers #dataanalytics #dataanalyst
#interviewquestion #sqlinterview
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: medium.com/@mahendraee204
Github: github.com/mahendra204
Here are table creation and insertion queries:
------------------------------------------------------------------------
create table WC_matches (
match_id int,
player_id int,
runs_scored int
);
create table WC_players (
id int,
name varchar(20)
);
insert into WC_matches values
(2401, 204, 60),
(2401, 105, 52),
(2401, 256, 88),
(2401, 245, 90),
(2401, 100, 75),
(2401, 128, 58),
(2402, 348, 50),
(2402, 105, 61),
(2402, 385, 63),
(2402, 128, 57),
(2403, 420, 52),
(2403, 120, 78),
(2403, 105, 80),
(2404, 256, 56),
(2404, 128, 70),
(2404, 245, 92);
insert into WC_players values
(100, 'iyer'),
(105, 'pant'),
(120,'Virat'),
(128, 'Rohit'),
(204, 'Klassen'),
(256, 'Vanderussain'),
(245, 'Hendricks'),
(348, 'Head'),
(385, 'marsh'),
(420, 'Gurbaz');

[@Vaibha293]

with cte as( select *,row_number() over(partition by player_id order by match_id)rn from WC_matches) select w.* from WC_players w Join ( select player_id from (select match_id,player_id, case when match_id-lag(match_id,1,match_id-1) over(partition by player_id order by match_id)=1 then 1 else 0 end cont from cte where player_id in (select player_id from cte where rn=3) )A group by player_id having count(player_id)=sum(cont))d on w.id=player_id

[@omilind]

select p.name as player_name, COUNT(m.runs_scored) half_century from wc_matches as m join wc_players as p on m.player_id=p.id where m.runs_scored>=50 and m.runs_scored=3

[@khadijasultana8964]

this will give no of half centuries but not hatrick...you need to find 3 consecutive 50s

[@VARUNTEJA73]

with cte as (select match_id-ranks as match_id ,player_id,name from ( select m.match_id,m.player_id,p.name, row_number()over(partition by player_id order by player_id)ranks from wc_matches m join wc_players p on m.player_id=p.id where runs_scored>50 group by match_id,player_id,name)t1) select name from cte group by match_id,player_id,name having count(match_id)=3 Is this right sir?

[@MeanLifeStudies]

Yes. But avoid complexity.

[@VARUNTEJA73]

@@MeanLifeStudies ok sir

[@VenkateshMarupaka-gn3rp]

My solution. WITH CTE AS (SELECT m.*, p.name, match_id -ROW_NUMBER() OVER(PARTITION BY m.player_id ORDER BY m.match_id) AS flag FROM WC_matches m JOIN WC_players p ON m.player_id = p.id), CTE1 AS (SELECT *, COUNT(1) OVER(PARTITION BY player_id,flag ORDER BY player_id) AS cnt FROM CTE) SELECT DISTINCT name FROM CTE1 WHERE runs_scored > 50 AND cnt =3

[@khadijasultana8964]

query is almost correct, runs_scored between 50 and 100 condition in first cte itself

[@dasubabuch1596]

with qer as ( select m.match_id,m.player_id,m.runs_scored,p.name from wc_matches m inner join wc_players p on m.player_id = p.id where m.runs_scored >= 50 ), ert as ( select q.*, row_number()over(partition by player_id order by match_id) as r from qer q ),hjk as ( select match_id,player_id,runs_Scored,name, match_id-r as f from ert ),uio as ( select match_id,player_id,runs_Scored,name,count(1)over(partition by player_id,f order by player_id) as f from hjk), tip as ( select player_id,f,name, count(*) as cnt from uio group by player_id,f,name having count(*) >= 3), eri as (select player_id, name from tip) select * from eri;

[@kushmanthreddy4762]

WITH cte AS ( SELECT player_id, match_id, COUNT(player_id) OVER (PARTITION BY player_id) AS c, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_id) AS rn FROM WC_matches ), ct2 AS ( SELECT player_id, (match_id - rn) AS di FROM cte WHERE c > 2 ), ct3 AS ( SELECT player_id, COUNT(*) OVER (PARTITION BY player_id, di) AS c22 FROM ct2 ) SELECT DISTINCT player_id FROM ct3 WHERE c22 = 3;