SQL Interview Question - Solution (Part

No video

SQL Interview Question - Solution (Part - XI) |

Рет қаралды 986

Күн бұрын

#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: / mahendraee204
Github: github.com/mah...
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');

Пікірлер: 12

@Ilovefriendswebseries 21 күн бұрын

with cte as ( select a.*,b.* from WC_matches a inner join WC_players b on a.player_id=b.id),cte2 as ( select cte.*,count(player_id) over(partition by player_id) as cnt from cte ) select name,count(name) from cte2 where cnt>=3 and runs_Scored >=50 group by name having count(name)>=3

@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 2 ай бұрын

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 2 ай бұрын

Yes. But avoid complexity.

@VARUNTEJA73 2 ай бұрын

@@MeanLifeStudies ok sir

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

@dasubabuch1596 2 ай бұрын

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;

@VenkateshMarupaka-gn3rp 2 ай бұрын

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