SQL Interview Question - Solution (Part - XI) |

Подписаться 771

50% 1

#education #sql #sqlinterview #dataengineers #dataanalytics #dataanalyst
#interviewquestion #sqlinterview
To Support use the Kofi link: ko-fi.com/kamireddymahendra
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');

Опубликовано:

25 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 11

@Vaibha293 6 дней назад

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

@dasubabuch1596 12 дней назад

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;

@VARUNTEJA73 12 дней назад

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 12 дней назад

Yes. But avoid complexity.

@VARUNTEJA73 12 дней назад

@@MeanLifeStudies ok sir

@omilind 10 дней назад

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 8 дней назад

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;

@VenkateshMarupaka-gn3rp 13 дней назад

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