Leetcode Hard problem 2| Tournament Winners | Complex SQL 8

[@abhishek_grd]

Providing the script is really helpful to practise and learn. May god bless you !!

[@ankitbansal6]

🙂

[@meharikahsay3015]

I second that. Thanks for providing the scripts to create the tables and insert statements Ankit!

[@ramakumarguntamadugu1299]

yes, really helpful to quickly get into the action ;)

[@akash4517]

Thank you Ankit : My Solution : %sql -- create table players -- (player_id int, -- group_id int) -- insert into players values (15,1),(25,1),(30,1),(45,1),(10,2),(35,2),(50,2),(20,3),(40,3); -- create table matches -- ( -- match_id int, -- first_player int, -- second_player int, -- first_score int, -- second_score int) -- insert into matches values (1,15,45,3,0),(2,30,25,1,2),(3,30,15,2,0),(4,40,20,5,2),(5,35,50,1,1); With finding_total_runs as ( select first_player as player_id,sum(first_score) as score from matches group by first_player union all select second_player as player_id,sum(second_score) as score from matches group by second_player ),total_runs as ( select player_id,sum(score) as score from finding_total_runs group by player_id ) ,finding_row_number as ( select group_id,player_id,score from ( select P.group_id,TR.player_id,TR.score,row_number() over (partition by P.group_id order by TR.score desc,TR.player_id ) as RN from total_runs TR left join players P on TR.player_id=P.player_id ) where RN=1 ) --select * from total_runs select * from finding_row_number

[@ankitbansal6]

Thanks for posting.

[@weshallbe]

Always learning something new from your channel, Ankit! I didn't know that ORDERING BY two columns in a window function works this way. Thank you again for such an awesome video.

[@ankitbansal6]

Happy to help😊

[@gauravgupta5530]

with cte as (select ROW_NUMBER() OVER(PARTITION BY group_id) as row_num, player_id, group_id, sum(IF(player_id=first_player, first_score, second_score)) as total_score from players INNER JOIN matches where (player_id = first_player or player_id = second_player) group by player_id, group_id order by group_id, total_score desc, player_id asc) select player_id, group_id from cte where row_num = 1; Your teaching got me to this solution

[@LS8636]

Keep doing good work bro. For me u are best SQL teacher online ..Great explanation..Thanks

[@mantisbrains]

select group_id, player from (select *, dense_rank() over (partition by group_id order by score desc,player asc) as rnk from (select group_id ,player , sum(score) as score from ((select group_id,first_player as player,first_score as score from players p join matches m on p.player_id = m.first_player union all select group_id, second_player, second_score from players l join matches k on l.player_id = k.second_player))a group by group_id, player)a)b where rnk = 1; Thanks a lot ,Ankit. In this video, I learnt how to apply two conditions in order by in case got two conditions to fulfill for ranking.

[@ramakumarguntamadugu1299]

thanks a lot for sharing the great content, I made a daily habit of watching your videos (at least one) and solve it... keep it up! lot of people like me are benefiting from your videos.

[@ankitbansal6]

Thank you. Keep going 😊

[@SACHINKUMAR-px8kq]

Good Habit

[@abb_raj1107]

After practicing sql several problems, my approach from start to end for this Que is the same as u approached. Thank u bhai. in later videos , don't hear baby crying as it add more spices like bg music 😁🥰♥.

[@meghnasoni]

Thank you so much for these videos Ankit. My approach is a little different: with player_score as ( SELECT first_player as pl, first_score as score from matches UNION all SELECT second_player as pl, second_score as score from matches ) SELECT group_id,pl,max(total_score) from (select distinct s.pl,p.group_id, sum(s.score) as total_score from player_score s join players p on s.pl=p.player_id group by 1)x group by 1 having max(total_score) order by pl

[@kartikeyasingh2798]

with cte as ( select first_player as playerid , first_score as runsscore from matches a union select second_player as playerid , second_score as runsscore from matches a ), ctea as ( select playerid ,sum(runsscore) as playerruns from cte group by playerid ) ,cteb as ( select A.playerid,A.playerruns, B.group_id, ROW_NUMBER() over (partition by B.group_id order by A.playerruns desc,A.playerid asc) as ranking from ctea A inner join players B on A.playerid=B.player_id ) select * from cteb C where C.ranking=1 ;

[@mranaljadhav8259]

Thank you so much Ankit so nicely explained .... My approach with cte as( select first_player as player_id , first_score as score from matches union all select second_player as player_id , second_score as score from matches), cte1 as (select player_id, sum(score) as total_score from cte c group by player_id) select p.group_id, c.player_id, max(total_score) as score from cte1 c, players p where c.player_id=p.player_id group by p.group_id;

[@ankitbansal6]

Great effort 😊

[@mranaljadhav8259]

@@ankitbansal6 Thanks alot... Because of you writing queries has been becoming easy for us

[@dfkgjdflkg]

you always put into service new concept, keep the good work. I see you as an outstanding instructor. thanks

[@ankitbansal6]

Glad to help

[@arpiteshsrivastava9838]

Thanks Ankit, wasn't aware the power of CTE & Window Fn.😲 while using multiple CTEs in a query ..dikhne me structure quite complex lagta h, but jo samjh gya..use likhne me mza ata h.!!😅 with cte as (select first_player player_id,first_score score from matches union all select second_player,second_score from matches), cte1 as (select c.player_id,p.group_id,sum(score) sum from cte c join players p on c.player_id = p.player_id group by player_id), cte2 as (select *, row_number() over(partition by group_id order by sum desc) as rn from cte1) select * from cte2 where rn = 1;

[@TusharKumar-u4p]

is this row_number same as rank() over?

[@Datapassenger_prashant]

again a wonderful video.. I tried to solve it on my own, but was stuck at how to get the last step that is, ranking.. I wasn't applying correct window function. so after watching your video, everything got clear and I was able to solve it. This playlist is amazing.

[@ankitbansal6]

Great job!

[@aaravkumarsingh4018]

I tried to break the problems into small chunks and finally i came to answer from myself, this approach i learned from u sir thanku so much sir😊😊😊😊. yes this query can also me conclude in less line but i go through your approach sir with cte as( select m.player_id,p1.first_player as player,p1.first_score as score,m.group_id from players m join matches p1 on p1.first_player=m.player_id union all select m.player_id,p2.second_player,p2.second_score,m.group_id from players m inner join matches p2 on p2.second_player=m.player_id), cte1 as(select player_id,sum(score) as total_score,group_id from cte group by group_id,player_id), cte2 as( select group_id,player_id,total_score ,rank() over(partition by group_id order by total_score desc,player_id) as rn from cte1) select group_id,player_id,total_score from cte2 where rn=1;

[@sayantanchowdhury9946]

use dense_rank as rank can give same number when the score is equal as dense rank can give proper ranking in this case

[@anupamsharma4263]

Tried it myself in first attempt, but before that have checked a lot of your videos which helped in understanding the process of 'How to approach these kind of problems': [with matches_transform as (select first_player as player_id, first_score as score from matches union all select second_player as player_id, second_score as score from matches), group_wise_total_player_score as (select p.group_id, mt.player_id, sum(mt.score) as total_player_score from matches_transform mt inner join players p on p.player_id = mt.player_id group by p.group_id, mt.player_id), max_score_group_wise as (select group_id, max(total_player_score) max_score_in_group from group_wise_total_player_score group by group_id) select msgw.group_id, min(gwtps.player_id) winners from group_wise_total_player_score gwtps inner join max_score_group_wise msgw on gwtps.group_id = msgw.group_id and gwtps.total_player_score = msgw.max_score_in_group group by msgw.group_id order by group_id];

[@e-explorewithabhishek]

Instead of this query Select * , rank() over(partition by group_id order by score desc, player_id asc) as rn from final_score; We should use Select * , row_number() over(partition by group_id order by score desc, player_id asc) as rn from final_score; Because, In first query, if score is same then we will get same rank number for both tie player_id. But In second query, If score is same then we will get different rank like lowest score we will get 1 rw_num and rw_num 2. then select * from final_ranking where rn=1 will work fine. Thanks for sharing knowledge .

[@ajaxaj6749]

These videos are really helpful. Please continue this initiative. God bless you.

[@ankitbansal6]

Sure. Thank you 😊

[@anandahs6078]

I tried this before trying your solution. you avoided two joins to players which is good in terms of optimization. I am very happy that i got to know your youtube channel, i may become sql champion like you if i keep practicing with amazing problems. sql is like a cake walk for me now after practicing your questions and approach that is followed. with total_points as( select player_id, group_id, sum(points) as total_points from ( select p1.player_id, group_id, first_score as points from matches m inner join players p1 on m.first_player = p1.player_id union all select p2.player_id, group_id, second_score as points from matches m inner join players p2 on m.second_player = p2.player_id ) a group by player_id, group_id ), rank_cte as ( select player_id, group_id, total_points, rank() over (partition by group_id order by total_points desc, player_id asc) rank from total_points ) select player_id, group_id, total_points from rank_cte where rank=1;

[@ankitbansal6]

Keep going 💪

[@anishchhabra6085]

Thank you for bringing up the problems that are paid on leetcode, I am sharing my solution for the above problem without looking into the video, please let me know your feedback: with ans as (select t.player,p.group_id,sum(t.score) as total_score, rank() over(partition by group_id order by sum(t.score) desc, player asc) as rnk from( select first_player as player,first_score as score from matches union select second_player as player,second_score as score from matches) t join players p on t.player = p.player_id group by player ) select group_id, player as winner from ans where rnk = 1;

[@priyachauhan813]

Very nicely explained. Like the approach of solving the heard leetcode problem. Thanks for posting such videos.

[@ankitbansal6]

Thank you 😊

[@gauravmalik3911]

with ct as (select *,case when (first_score > second_score) or (first_score = second_score and first_player < second_player) then first_player else second_player end winners, case when (first_score > second_score) or (first_score = second_score and first_player < second_player) then first_score else second_score end winners_score from matches) select group_id, winners, winners_score from ct left join players on ct.winners = players.player_id where (group_id, winners_score) in (select group_id, max(winners_score) max_score from ct left join players on ct.winners = players.player_id group by 1)

[@arthurmorgan9010]

sir my solution to the problem: with cte as ( select *, case when first_score>second_score then first_player when second_score > first_score then second_player when first_score = second_score then (select case when first_player>second_player then second_player else first_player end) end as winner, case when first_score > second_score then first_score when second_score > first_score then second_score when first_score = second_score then first_score end as score from matches ) ,cteone as ( select c.winner,c.score,p.group_id, dense_rank() over(partition by group_id order by score desc, winner asc) as by_win from cte c join players p on c.winner = p.player_id ) select cto.winner,cto.score,cto.group_id from cteone cto where by_win = 1

[@ankitbansal6]

Great effort 😊

[@anirvansen5024]

Hi Ankit , My approach to these type of questions is to find the input dataset first , like how the structure should look like for the dataset where i can apply my logic , is it the correct approach or how do you approach these complex questions ?

[@jainishchuri6449]

slight different approach = with t1 as( select p.player_id,p.group_id,isnull(sum(fp.first_score),0)+isnull(sum(sp.second_score),0) total_points, rank () over (partition by p.group_id order by(isnull(sum(fp.first_score),0)+isnull(sum(sp.second_score),0))desc) as r from players p left join matches fp on fp.first_player = p.player_id left join matches sp on sp.second_player = p.player_id group by p.player_id,p.group_id ), t2 as( select player_id,group_id,total_points, rank () over (partition by group_id order by( player_id)) as pr from t1 where r =1 ) select * from t2 where pr = 1

[@2412_Sujoy_Das]

I believe it is a premium question at LeetCode, sir?? But I solved it anyways.... (interpreted it differently at first though)...... Thank you sir!

[@ayushtyagi4240]

my sol : with temp1 as (select first_player as player , first_score as score from matches ) , temp2 as ( select second_player as player , second_score as score from matches ) , temp3 as ( select * from temp1 Union all select * from temp2 ) , temp4 as (select player , group_id ,sum(score) score from temp3 inner join players on players.player_id = temp3.player group by player order by group_id , player ) , temp5 as (select * , row_number() over (partition by group_id order by score desc ) maxscore from temp4 ) select group_id , player from temp5 where maxscore = 1

[@Ali-q4d4c]

THANKS FOR SHARING THE KNOWLEDGE

[@shivarajhalageri2513]

Thank you very much Ankit learnt a lot from this 🙌

[@ankitbansal6]

Awesome 👍

[@shivarajhalageri2513]

@@ankitbansal6 How can I practice leetcode problems for free it will be a great help you could help me in this regard

[@ankitbansal6]

@@shivarajhalageri2513 i have a playlist for Leetcode hard problems

[@NatureAndTravelAndAnimals]

Good explanation and easy to understand.

[@ankitbansal6]

Thank you 😊

[@pradeepkumarc2829]

Hi Ankit, Loved your video 👏👏👏👏 You could have change column name first name to score, score to playerID or let me know is there reason to take like that ??

[@ankitbansal6]

Thank you :) Didn't get you. First_player and second_player are player_id only and first_score and second_score are their respective scores.

[@ls47295]

Great Work Ankit. Really appreciated with Players_with_group_score as ( select group_id,Player,sum(score) as Total_score ,Dense_Rank() Over( Partition By group_id Order by sum(score) desc, player asc) as rnk from ( select group_id,first_player as Player,sum(first_score) as score from matches m Inner JOIN players p1 ON m.first_player = p1.player_id group by group_id,first_player UNION ALL select group_id,second_player as Player,sum(second_score) as score from matches m Inner JOIN players p2 ON m.second_player = p2.player_id group by group_id,second_player ) a Group by group_id,Player order by group_id desc,Player desc ) select group_id,player as winner from Players_with_group_score where rnk =1

[@ankitbansal6]

Thanks for posting 👏

[@suriyas6338]

Hi Ankit, My Solution :) with cte1 as ( select m.first_player as player, m.first_score as player_score, fp.group_id as player_group_id from matches m inner join players fp on fp.player_id = m.first_player union select m.second_player as player, m.second_score as player_score, sp.group_id as player_group_id from matches m inner join players sp on sp.player_id = m.second_player ) ,cte2 as ( select *, max(player_score) over(partition by player_group_id order by player_group_id ) as max_scre_by_grp, sum(player_score) over(partition by player order by player) as per_players_scre from cte1 ) select distinct player_group_id as group_id, min(case when max_scre_by_grp = per_players_scre then player else null end) over(Partition by player_group_id order by player_group_id) as winner, max_scre_by_grp as score from cte2 Happy learning guys !!

[@RupeshKumar-kq1bs]

You are doing God's work. Thank you

[@ankitbansal6]

Thank you 😊

[@gauravsoni3530]

really useful videos keep up the good work

[@ankitbansal6]

Glad you like them!

[@somalisengupta2397]

I did it a little differently but got stuck with the ties part "with cte as(select n.second_player as players_id,n.second_score as score, p.group_id as groups from players p INNER JOIN matches n on p.player_id=n.second_player UNION ALL select m.first_player as players_id,m.first_score as score,p.group_id as groups from players p INNER JOIN matches m on p.player_id=m.first_player) ,scorecard as (select players_id,sum(score) as total_scored, groups, rank() over(partition by groups order by sum(score) desc) as rn from cte group by players_id, groups) select players_id, total_scored, groups, rn from scorecard where rn=1"

[@mohit231]

Loved your video and how you break the problem Can you please share the insert script I want to try it out as well Great job

[@ankitbansal6]

It's there in description box.

[@gagansingh3481]

Loved the videos brother ❤ keep posting it 👍

[@ankitbansal6]

Thank you 😊

[@kanchankumar3355]

Great explanation!! Thanks

[@ankitbansal6]

🙏

[@SudhirKumar-rl4wt]

thanks for the question and solution.

[@ankitbansal6]

Welcome

[@ajaxaj6749]

select first_player,first_score into #tmp from matches union all select second_player,second_score from matches select * from (select a.group_id,b.first_player,a.player_id,b.first_score ,dense_rank() over(partition by a.group_id order by first_score desc,player_id) as rnk from players a left join #tmp b on a.player_id=b.first_player) a where rnk=1

[@ankitbansal6]

Good job 👍

[@Dhanushts-g7x]

with cte1 as (select first_player,max(first_score) s from (SELECT first_player,first_score from matches union all select second_player,second_score from matches) a group by first_player order by first_player), cte2 as (select *,row_number() over(partition by group_id order by s desc,player_id) rnk from cte1 join players p on cte1.first_player = p.player_id ) select player_id,group_id,s score from cte2 where rnk=1 (easy solution)

[@bukunmiadebanjo9684]

My attempt was quite different towards the last CTE Here is a look at my attempt with score_table as (select m.match_id, m.first_player as player_id, m.first_score as player_score from matches m union all select mm.match_id, mm.second_player as player_id, mm.second_score as player_score from matches mm), ranking as (select s.player_id, p.group_id,sum(s.player_score) as total_score, DENSE_RANK() over(partition by p.group_id order by sum(s.player_score) DESC) as rnk from score_table s inner join players p on s.player_id = p.player_id group by s.player_id, p.group_id) select min(player_id) as player_id, group_id,total_score from ranking where rnk = 1 group by group_id, total_score

[@ThunderIndian]

Just add sum(s.score) over(partition by group_id) as total_score instead of sum(score)

[@kumarakhil9593]

My Solution: -- creating a cte containing the player_id and total score obtained by them with cte as ( select player,sum(score) as score from ( select first_player as player,first_score as score from matches union all select second_player as player,second_score as score from matches) group by 1 ) -- Finding the player having maximum score in each group select player, group_id from( select *,rank() over(partition by p.group_id order by c.score desc, c.player) as rnk from cte c join players p on c.player=p.player_id) where rnk=1

[@anushakrishnaa]

with cte as( select match_id, first_player as player_id,first_score as runs from matches union select match_id,second_player as player_id ,second_score as runs from matches),cte2 as( select c.* ,p.group_id from cte c inner join players p on c.player_id =p.player_id),cte3 as( select *,DENSE_RANK() over(partition by group_id order by runs desc ,player_id ) rk from cte2) select * from cte3 where rk=1

[@arpiteshsrivastava9838]

Ankit, It would be really helpful if you could provide us all SQL Leetcode Questions. Thankyou!

[@ankitbansal6]

As soon as possible

[@PrashantSharma-sw2jr]

with player_score as ( Select b.group_id,a.PlayerID,sum(a.Score) as Score from ( Select first_player as PlayerID, first_score as score from matches union all Select second_player,second_score as score from matches ) a left join players b on a.playerid = b.player_id group by b.group_id,a.PlayerID order by 1 asc,2 asc ) , player_rank as( Select *,dense_rank(score) over(partition by group_id order by score desc,PlayerID asc ) as rnk from player_score ) Select * from player_rank where rnk=1 order by group_id asc, rnk asc

[@Tusharchitrakar]

This doesn't seem hard though as the logic is pretty straight forward. Seems more like a medium level leetcode problem.

[@thegamingtron8698]

Great explanation

[@singarampalaniappan1736]

Good one buddy ! You are doing a great job

[@ankitbansal6]

Thank you 😊

[@naveenreddy5994]

Great video, another solution. with cte as ( SELECT d.*, rank() over(partition by d.group_id order by tot_score desc) as rnk FROM ( select p.group_id, p.player_id, SUM(COALESCE(fp.first_score, 0) + COALESCE(sp.second_score, 0)) as tot_score from players p LEFT join matches fp on p.player_id = fp.first_player LEFT join matches sp on p.player_id = sp.second_player group by p.group_id, p.player_id --order by group_id, player_id )d ) select c1.group_id, c1.player_id from cte c1 where rnk = 1 and player_id = (select min(player_id) from cte c2 where c1.group_id = c2.group_id and c2.rnk = 1)

[@HimanshuShaandilya]

bro just one question why we are not taking second score of first player and first score of second player while doing union?

[@amithgshetty8171]

its simple, the first score column belongs only to the first player and second score belongs only to the second player in the tables, we cannot use viceversa here

[@vyabinivenkatesan7839]

My solution for the problem: with cte as( Select p.player_id, sum(coalesce(m.first_score,0)+coalesce(m1.second_score,0)) total_scores from players p left join matches m on p.player_id=m.first_player left join matches m1 on p.player_id=m1.second_player group by p.player_id), cte1 as( Select p.*,cte.total_scores,dense_RANK() over (partition by group_id order by cte.total_scores desc) rank from players p inner join cte on cte.player_id=p.player_id ), cte2 as( Select player_id,group_id,total_scores,ROW_NUMBER() over(partition by group_id order by player_id asc) rn from cte1 where rank=1) Select player_id,group_id,total_scores from cte2 where rn=1

[@vikhyatjalota2213]

My attempt a little lengthy but hehe : with cte_1 as ( select m.*,group_id, case when first_score > second_Score then first_player when first_score second_Score then first_score when first_score

[@arjunanand1541]

Alternate solution: with t1 as( select player,sum(pts) as pts from( select first_player as player,sum(first_score) as pts from matches group by first_player union all select second_player as player,sum(second_score) as pts from matches group by second_player )a group by player ),t2 as( select group_id,player_id,pts,max(pts) over(partition by group_id) as max_pts from( select players.*,t1.pts from players join t1 on players.player_id = t1.player)b ) select group_id,pts,min(player_id) as winner from t2 where pts=max_pts group by group_id,pts

[@PraveenSinghRathore-df3td]

with individual_scores as (select first_player as player, first_score as score from matches union select second_player as player, second_score as score from matches), total_scores as (select player, sum(score) as total_score from individual_scores group by 1 order by 1), player_group as (select ts.*, p.group_id from total_scores ts join players p on ts.player = p.player_id), winner_list as (select *, row_number() over(partition by group_id order by total_score desc,player asc) as rnk from player_group) select player,total_score, group_id from winner_list where rnk = 1;

[@nijamuddinshaik9130]

select group_id,first_player from(select group_id,first_player, rank() over(partition by group_id order by sum(first_score)desc,player_id asc) as v from(select match_id,first_player,first_score from matches union all select match_id,second_player,second_score from matches)a, players b where a.first_player=b.player_id group by group_id,first_player) where v=1;

[@Chathur732]

with cte as (select first_player as player_id , first_score as score from matches union all select second_player as player_id , second_score as score from matches order by player_id), cte_2 as (select P.group_id, P.player_id, coalesce(score,0) as score from players P left join cte C on P.player_id=C.player_id) select * from ( select group_id, player_id,score, dense_rank() over(partition by group_id order by score desc,player_id asc) as ranking from cte_2) a where ranking = 1 happy SQL coding!! :):)

[@PriyaYadav-jh5wj]

Solution: with cte as (select m.match_id,m.first_player,p.group_id,m.first_score from matches m left join players p on m.first_player= p.player_id union select m.match_id,m.second_player,p.group_id,m.second_score from matches m left join players p on m.second_player= p.player_id), new_rank as( select c.group_id,c.first_player,sum(c.first_score), row_number() over (partition by group_id order by sum(first_score) desc, first_player asc) as rnk from cte c group by c.group_id,c.first_player order by group_id) select group_id,first_player,sum from new_rank where rnk=1

[@ankitbansal6]

Thanks good effort

[@murwathmehtar861]

with q1 as ( select c.group_id, c.player_id, sum(c.score) as score from ( select a.group_id, b.first_player as player_id, b.first_score as score from players a, matches b where a.player_id=b.first_player union select a.group_id, b.second_player as player_id, b.second_score as score from players a, matches b where a.player_id=b.second_player ) c group by c.group_id, c.player_id order by c.group_id, score desc ) select group_id, player_id, score from ( select *, row_number() over (partition by group_id) as rn from q1 ) d where d.rn=1;

[@anirvansen2941]

MYSQL Solution with base as ( select first_player as player_id, first_score as score from matches UNION ALL select second_player as player_id, second_score as score from matches ), base_added as ( select b.*,p.group_id,sum(score) as total_score from base b join players p on b.player_id = p.player_id group by b.player_id,p.group_id) select distinct group_id,first_value(player_id) over(partition by group_id order by total_score desc,player_id asc) as player_id from base_added;

[@khushboobansal2312]

with cte as ( Select player_id,group_id,total_score, dense_rank() over (partition by group_id order by total_score desc,player_id) as rn from ( Select p.*,(coalesce(m.first_score,0)+coalesce(m1.second_score,0)) as total_score from Players p left join matches m on p.player_id = m.first_player left join matches m1 on p.player_id = m1.second_player) group by 1,2,total_score) Select * from cte where rn = 1 (I also tried with less ctes)

[@oorjamathur8459]

My solution: with tb_player_scores as (select first_player as player, first_score as score from matches union all select second_player as player, second_score as score from matches) , tb_final_scores as (select player, sum(score) as total_score from tb_player_scores group by player) , tb_player_group_scores as (select player_id, group_id, isnull(total_score,0) as player_score, rank() over (partition by group_id order by isnull(total_score,0) desc, player_id asc) as rn from players left join tb_final_scores on players.player_id=tb_final_scores.player) select group_id, player_id as winner, player_score from tb_player_group_scores where rn = 1;

[@munmundey149]

We can do it in another way without CTE. select a.player,a.group_id,a.total_score from (select m.player,p.group_id, sum(score) as total_score, rank() over (partition by p.group_id order by sum(score) desc,player asc) as rn from ( select first_player as player, first_score as score from matches union ALL select second_player as player, second_score as score from matches order by player) m inner join players p on p.player_id=m.player group by m.player) a where a.rn =1;

[@rajataggarwal1343]

My Solution -- with cte as (select player_id,group_id, first_score as score from players p join matches m on p.player_id = m.first_player union all select player_id,group_id, second_score as score from players p join matches m on p.player_id = m.second_player), cte2 as (select group_id,player_id, sum(score) as total_points, rank() over (partition by group_id order by sum(score) desc, player_id asc) as ranking from cte group by group_id, player_id) select * from cte2 where ranking = 1

[@rahulmehla2014]

my approach: with allmatch as( select first_player as player, first_score as score from matches union all select second_player, second_score from matches), PlayerTotal as( select player,sum(score) as score from allmatch group by player), rnkTable as( select p.*,t.score,dense_rank() over(partition by p.group_id order by score desc,player_id) as rn from players p inner join PlayerTotal t on p.player_id = t.player) select * from rnkTable where rn = 1

[@nandhininatarajan7662]

Here is my approach to solve this problem(used nested case when) with cte as ( select *, case when first_score > second_score then first_score else second_score end as winning_score, case when first_score > second_score then first_player when first_score = second_score then (case when first_player < second_player then first_player else second_player end) else second_player end as winning_player from matches ) select * from( select p.group_id,m.winning_player,rank () over (partition by p.group_id order by m.winning_score desc) as rn from cte m inner join players p on p.player_id=m.winning_player) a where rn=1;

[@LearnYouAndMe]

here is my solution using joins with set1 as ( select p.group_id,p.player_id, sum(isnull(m1.first_score,0) + isnull(m2.second_score,0)) as TotalScore from players as p left join matches as m1 on m1.first_player=p.player_id left join matches as m2 on m2.second_player=p.player_id group by p.player_id,p.group_id ), set2 as ( select t.*, ROW_NUMBER() over(partition by group_id order by t.TotalScore desc,t.player_id ) as SubSr from set1 as t ) select * from set2 as t where t.SubSr=1

[@themightyvk]

Doing grt job...

[@themightyvk]

Can you share any link for rows between and indexes and stored procedure

[@anirvansen5024]

MY SQL Solution with base as ( select t1.first_player, t2.group_id as first_player_group, t1.first_score, t1.second_player, t3.group_id as second_player_group, t1.second_score from matches t1 join players t2 on t1.first_player = t2.player_id join players t3 on t1.second_player = t3.player_id ), flat_dataset as ( select first_player_group as group_id, first_player as player_id, first_score as score from base UNION ALL select second_player_group as group_id, second_player as player_id, second_score as score from base ), sum_dataset as ( select *, sum(score) over(partition by group_id,player_id) as score_sum from flat_dataset order by group_id,player_id ) select distinct group_id,first_value(player_id) over(partition by group_id order by score_sum desc,player_id) as highest_score_in_group from sum_dataset;

[@SACHINKUMAR-px8kq]

EveryONE become SQL teacher ,until "AB COMING

[@ankitbansal6]

A big compliment for me 🙏

[@SACHINKUMAR-px8kq]

@@ankitbansal6 Yes Sir

[@rahuldave6699]

With temp1 as( SELECT FIRST_PLAYER,SUM(FIRST_SCORE) as sum1 from( SELECT first_player, first_score from matches union all select second_player as first_player, second_score as first_score from matches ) tmp group by first_player), result as ( select player_id,sum1,group_id, dense_rank() over(partition by group_id order by sum1 desc,player_id asc) as rnk from temp1 inner join players on temp1.FIRST_PLAYER = players.player_id) select player_id,group_id from result where rnk=1

[@utkarshtrivedi9949]

My solution: with t1 as( select player_id, sum(score) as total_score from ( select first_player as player_id, first_score as score from matches UNION ALL select second_player as player_id, second_score as score from matches ) t1 group by 1 ), comb as( select t1.*, p.group_id, rank() over(partition by p.group_id ORDER by t1.total_score desc, t1.player_id ASC ) as ranks from t1 join players as p on t1.player_id= p.player_id ) select group_id, player_id from comb where ranks=1

[@ankitbansal6]

Perfect

[@amangupta1959]

My Solution in MySQL: SELECT * FROM (SELECT *, Row_number() OVER ( partition BY group_id ORDER BY player_id) AS rn FROM (SELECT t.player_id, t.group_id, Sum(t.score) AS score, Max(Sum(t.score)) OVER( partition BY t.group_id) AS max_score FROM (SELECT p.*, m.first_player, m.second_player, m.first_score, m.second_score, CASE WHEN p.player_id = m.first_player THEN first_score ELSE second_score END AS score FROM players p LEFT JOIN matches m ON p.player_id = m.first_player OR p.player_id = m.second_player) t GROUP BY player_id, group_id) x WHERE score = max_score) final WHERE rn = 1;

[@ankitbansal6]

Thanks for posting. Keep rocking.

[@pratikedu8331]

WITH SCORES AS ( SELECT PLAYER, SUM(SCORE) AS TOTAL_SCORE FROM ( SELECT FIRST_PLAYER AS PLAYER , FIRST_SCORE AS SCORE FROM MATCHES UNION ALL SELECT SECOND_PLAYER AS PLAYER , SECOND_SCORE AS SCORE FROM MATCHES ) GROUP BY 1 ) SELECT GROUP_ID,PLAYER FROM ( SELECT S.PLAYER, S.TOTAL_SCORE , P.GROUP_ID, ROW_NUMBER() OVER (PARTITION BY P.GROUP_ID ORDER BY S.TOTAL_SCORE DESC, S.PLAYER ASC) AS WINNER FROM SCORES AS S LEFT JOIN PLAYERS AS P ON S.PLAYER=P.PLAYER_ID ) WHERE WINNER = 1

[@edumail1016]

WITH win_flag AS ( SELECT match_id, CASE WHEN first_score>second_score THEN first_player WHEN first_score

[@Vaibha293]

with cte as( select first_player player,sum(first_score)score from matches group by first_player union all select second_player,sum(second_score)score from matches group by second_player),cte2 as( select player,sum(score)score from cte group by player),final as( select p.*,ISNULL(score,0)score,dense_rank() over(partition by group_id order by ISNULL(score,0) desc,player_id )rn from players p left join cte2 c on p.player_id=c.player) select * from final where rn=1

[@ranaaditya7960]

withour RANK() and ROW_NUMBER() Here is my query : with ftable as ( with player_score as (select first_player as player , first_score as score from Matches UNION select second_player as player , second_score as score from Matches) select p.group_id , ps.player , sum(score) as score from player_score ps JOIN Players p ON p.player_id = ps.player group by p.group_id,ps.player ) select f1.group_id , f1.player from ftable f1 where f1.score = (select max(score) from ftable f2 where f2.group_id = f1.group_id);

[@pavanreddy5724]

--solution with player_score as( select player,sum(score) score from (select first_player as player,first_score as score from matches union all select second_player as player,second_score as score from matches)group by 1), result as(select player_id,group_id ,player,score from players a join player_score b on a.player_id= b.player) select group_id,Player_id,score from (select group_id,player_id,score,row_number() over (partition by group_id order by score desc, player_id asc )r from result)where r=1 ;

[@shekharagarwal1004]

Thanks @Ankit Bansal for the excellent video -Keep Going !!!. Please find the solution from my end - with match_info as ( select m.first_player as player,m.first_score as score, p.group_id from matches m inner join players p on m.first_player=p.player_id union all select m.second_player as player,m.second_score as score, p.group_id from matches m inner join players p on m.second_player=p.player_id) , final_result as (Select m.* , DENSE_RANK() over ( partition by group_id order by group_id,score desc,player ) as ranking from match_info m ) select group_id,player,score from final_result where ranking=1

[@siddheshkalgaonkar2752]

The below solution gives you more readability in PostgreSQL: with only_maximum_scores as ( (select first_player as player_id,first_score as score from matches order by first_player) union all (select second_player as player_id,second_score as score from matches order by second_player) ) , player_aggregated_scores as ( select player_id,sum(score) as max_score from only_maximum_scores group by player_id ) , get_group_id as ( select a.player_id,a.max_score,b.group_id from player_aggregated_scores a join players b on a.player_id=b.player_id ) , get_max_from_each_group as ( select group_id,min(player_id),max(max_score) as max_score from (select group_id,player_id,max_score,dense_rank() over(partition by group_id order by max_score desc) rnk from get_group_id) a where rnk=1 group by group_id ) select * from get_max_from_each_group;

[@ankitbansal6]

Thanks for posting. This looks clean 👍

[@cuongtranmanh2287]

with a as (select first_player p, sum(first_score) s from matches group by 1 union ALL select second_player p, sum(second_score) s from matches group by 1 ), b as (SELECT p, sum(s) total from a group by 1), c as (SELECT * from players inner join b on b.p=players.player_id), d as (select *, row_number() over (PARTITION by group_id order by total desc, p asc) as rn from c) select group_id, player_id from d where rn=1

[@shivkumar-vs8vt]

Bhai ye code kha milega likha hua

[@ankitbansal6]

Apne aap likhna padega Bhai 😊

[@exanode]

Sharing my approach : select * from ( with player_scores as ( select player,sum(score) Total_Score from (select first_player player, first_score score from matches UNION ALL select second_player player, second_Score score from matches) group by player ) select p.*,DENSE_RANK() OVER(PARTITION BY GROUP_ID ORDER BY TOTAL_SCORE DESC NULLS LAST,p.PLAYER_ID) Rank, s.Total_Score from players p left join player_scores s on p.player_id = s.player ) where rank = 1

[@jatinyadav8960]

with t1 as ( select case when first_score>=second_score then first_player else second_player end as player, case when first_score>=second_score then first_score else second_score end as score from matches) select group_id,player_id,max(score) as score from t1 join players on t1.player=players.player_id group by group_id order by group_id

[@addhyasumitra90]

with CTE as ( select first_player as player, first_score as score from matches UNION ALL select second_player as player, second_score as score from matches), CTE2 as( select player, SUM(score) as total_score from CTE group by player CTE3 as ( select p.player_id, p.group_id, total_score from players as p JOIN CTE2 on p.player_id= CTE2.player), CTE4 as ( select *,RANK() over(partition by group_id order by total_score desc, player_id) as winner from CTE3) select group_id, player_id, total_score from CTE4 where winner=1;

[@simardeepsingh]

with cte as( select match_id, first_player, first_score from matches UNION ALL select match_id, second_player, second_score from matches ), cte_2 as ( select first_player, group_id, sum(first_score)as total_score , dense_rank() over(partition by group_id order by sum(first_score) desc, first_player asc) as rnk from cte inner join players on cte.first_player = players.player_id group by first_player, group_id ) select * from cte_2 where rnk =1

[@Mathematica1729]

WITH CTE AS (SELECT first_player as p_id,first_score as score FROM matches UNION ALL SELECT second_player,second_score FROM matches) SELECT group_id,player_id as winner,total_score FROM (SELECT group_id,player_id,total_score, RANK() OVER (partition by group_id order by total_score desc,player_id) as rnk FROM (SELECT c.p_id,SUM(score) as total_score,p.player_id,p.group_id FROM CTE c INNER JOIN players p on c.p_id=p.player_id GROUP BY p.player_id)M)N WHERE rnk=1; I used subqueries after CTE, is it fine or CTE is better to use?

[@himanshumittal1095]

with cte as ( select first_player as player_id, first_score as score from matches union all select second_player as player_id, second_score as score from matches ), results as ( select cte.player_id ,players.group_id, sum(score) as total_score , dense_rank() over (partition by players.group_id order by total_score desc) as rank from cte inner join players on players.player_id= cte.player_id group by cte.player_id, players.group_id ) select min(player_id), group_id from results where rank=1 group by group_id;

[@vandanaK-mh9zo]

with cte as ( select first_player as player, first_score as score from matches union all select second_player as player, second_score as score from matches), cte2 as ( select p.PLAYER_ID, p.GROUP_ID, sum(nvl(c.score,0)) as total_score from players p left join cte c on p.player_id = c.player group by p.PLAYER_ID, p.GROUP_ID order by group_id, player_id), cte3 as ( select *, rank() over (partition by group_id order by total_score desc, player_id asc) as rnk from cte2) select group_id, player_id from cte3 where rnk =1 ;

[@litheshraju5207]

with total_score as (select first_player player_id,sum(first_score) over(partition by first_player) tot_score from matches union select second_player player_id,sum(second_score) over(partition by second_player) tot_score from matches) ,tot_points_per_player as (select player_id,sum(tot_score) final_score,max(tot_score) top_score from total_score group by player_id) select a.player_id,a.group_id,a.dr from (select p.player_id,p.group_id,tp.final_score,DENSE_RANK() over(partition by p.group_id order by final_score desc, p.player_id asc) dr from players p inner join tot_points_per_player tp on p.player_id=tp.player_id) a where a.dr=1

[@ankitbansal6]

Thanks for posting 👏

[@mayankbhardwaj1487]

my Approach with one as ( select player, group_id, sum(score) as total_score from ( select first_player as player, sum(first_score) as score from matches group by first_player union all select second_player as player, sum(second_score) as score from matches group by second_player) x join players on players.player_id=x.player group by player,group_id) select group_id,max(total_score) as winning_score from one group by group_id

[@mr.pingpong502]

with cte as( select player_id,group_id,second_player as oppo_player,first_score as score from matches a inner join players b on a.first_player=b.player_id union all select player_id,group_id,first_player as oppo_player,second_score as score from matches a inner join players b on a.second_player=b.player_id ) ,cte2 as ( select player_id,group_id,total_player_points,row_number() over(partition by group_id order by total_player_points desc,player_id) as ranks from ( select player_id,sum(score) as total_player_points,group_id from cte group by player_id,group_id )a ) select * from cte2 where ranks=1

[@sandeepagarwal7155]

with high_Score as (select player,sum(score) as total_score from ( Select first_player as player,first_score as score from matches union ALL Select second_player as player,second_score as score from matches) group by 1) select * from ( select players.*,high_Score.total_score, rank() over(partition by group_id order by total_score desc, player_id asc) grank from players inner join high_Score on players.player_id = high_score.player) where grank = 1

[@akashkonda8385]

with cte1 AS (select first_player, first_score from matches), cte2 AS (select second_player, second_score from matches), cte3 AS (select p.player_id, p.group_id, ISNULL(c.first_score,0) + ISNULL(b.second_score,0) as total_score_of_player from players p left join cte1 c on p.player_id = c.first_player left join cte2 b on p.player_id = b.second_player) select * from (select *, rank() over(partition by group_id order by total_score_of_player desc, player_id asc) as rnk from cte3) as t where t.rnk = 1

[@sushmitamaity9490]

Select * From ( SELECt player_id,group_id,rank()over(partition by group_id order by score desc,player_id asc) AS Rnk FROM( select player_id, group_id, SUM(CASE WHEN f.first_score IS NOT NULL THEN f.first_score ELSE S.second_score END) AS SCORE FROM players P LEFT OUTER JOIN matches f ON (P.player_id = f.first_player) LEFT OUTER JOIN matches S ON (P.player_id = S.second_player) GROUP BY 1,2)a)b where rnk=1;

[@techmania979]

I used below query, with cte as ( select player_id,first_score score,group_id from matches m inner join players p on p.player_id=m.first_player union all select player_id,second_score score,group_id from matches m inner join players p on p.player_id=m.second_player ), cte1 as( select player_id,group_id, sum (score) as s, DENSE_RANK() Over(partition by group_id order by sum (score) desc,player_id asc ) rn from cte group by player_id,group_id) select * from cte1 where rn=1

[@arjitakashyap8169]

--select * from players --select * from matches select * from ( select group_id,player,total_Score = sum(score) , rn = dense_rank () over (partition by group_id order by sum(score) desc,player) from ( select group_id,player = m.first_player,score = m.first_Score from matches m inner join players p on m.first_player = p.player_id union all select group_id,m.second_player,m.second_Score from matches m inner join players p on m.second_player = p.player_id )a group by group_id,player )c where rn = 1

[@addhyasumitra90]

with CTE as ( select m1.first_player as player, m1.first_score as score, p1.group_id from matches1 as m1 join players as p1 ON m1.first_player=p1.player_id UNION all select m1.second_player, m1.second_score, p2.group_id from matches1 as m1 join players as p2 ON m1.second_player=p2.player_id), finalist as ( select group_id, player, SUM(score) as total_score , DENSE_RANK() OVER(Partition by group_id order by SUM(score) desc, player) as rn from CTE group by group_id, player --order by group_id, player ) select * from finalist where rn=1;

[@DeepakKumar-xo1hk]

with cte1 as ( select player_id, SUM(score) score from ( select first_player as player_id, first_score as score from matches union all select second_player as player_id, second_score as score from matches ) T group by player_id ), cte2 as ( select p.group_id, p.player_id, cte1.score, DENSE_RANK() over (partition by group_id order by score desc, cte1.player_id asc) as r from cte1 join players p on cte1.player_id = p.player_id ) select group_id, player_id, score from cte2 where r = 1;