SQL Interview Question - Solution (Part - XI) |

#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');

[@Ilovefriendswebseries]

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

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

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

[@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]

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]

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