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

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

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

[@LS8636]

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

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

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

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

[@hairavyadav6579]

Nice explanation
My approach
with cte as(select p.group_id,p.player_id, sum(ifnull(m.first_score,0) + ifnull(m2.second_score,0)) as total
from players as p left join matches as m on p.player_id=first_player left join matches as m2 on p.player_id=m2.second_player group by 1,2)
select * from (select *, dense_rank() over( partition by group_id order by total desc,player_id asc) as rnk from cte) sal where rnk =1;

[@dfkgjdflkg]

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

[@ankitbansal6]

Glad to help

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

[@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 😁🥰♥.

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

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

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

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

[@priyachauhan813]

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

[@ankitbansal6]

Thank you 😊

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

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

[@Ali-q4d4c]

THANKS FOR SHARING THE KNOWLEDGE

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

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

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

[@NatureAndTravelAndAnimals]

Good explanation and easy to understand.

[@ankitbansal6]

Thank you 😊

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

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

[@gauravsoni3530]

really useful videos keep up the good work

[@ankitbansal6]

Glad you like them!

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

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

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

[@gagansingh3481]

Loved the videos brother ❤ keep posting it 👍

[@ankitbansal6]

Thank you 😊

[@RupeshKumar-kq1bs]

You are doing God's work. Thank you

[@ankitbansal6]

Thank you 😊

[@SudhirKumar-rl4wt]

thanks for the question and solution.

[@ankitbansal6]

Welcome

[@thegamingtron8698]

Great explanation

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

[@singarampalaniappan1736]

Good one buddy ! You are doing a great job

[@ankitbansal6]

Thank you 😊

[@kanchankumar3355]

Great explanation!! Thanks

[@ankitbansal6]

🙏

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

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

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

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

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

[@kunaljain-l8l]

was able to solve it in 14 mins , not sure if it will take more time i interview condition .Please suggest a way to improve timing and do it faster

[@ankitbansal6]

Practice practice practice

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

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

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

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

[@arpiteshsrivastava9838]

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

[@ankitbansal6]

As soon as possible

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

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

[@themightyvk]

Doing grt job...

[@themightyvk]

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

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

[@Ankitatewary-q6w]

with 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
)
,total_scores as
(
select
player,
sum(score) as total_scores
from scores
group by player
)
,compare as
(
select
p.player_id,
p.group_id,
ts.total_scores as total_score,
max(ts.total_scores) over(partition by p.group_id) as max_group_score
from players p
join total_scores ts on p.player_id=ts.player
group by p.player_id
)
select
group_id,
min(player_id) as player
from compare
where total_score=max_group_score;
group by group_id;

[@Tusharchitrakar]

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

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

[@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!! :):)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@shivkumar-vs8vt]

Bhai ye code kha milega likha hua

[@ankitbansal6]

Apne aap likhna padega Bhai 😊

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

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

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

[@Anonymous-im2lx]

with cte as (select player_id , group_id , case when first_player = player_id then first_score else 0 end
+ case when second_player=player_id then second_score else 0 end as total_score
from players p left join matches m on p.player_id = m.first_player or p.player_id = m.second_player
),
cte2 as (select player_id , group_id , sum(total_score) as total_score , row_number() over (partition by group_id order by sum(total_score) desc , player_id) as rn from cte group by player_id, group_id)
select * from cte2 a where rn=1;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@avinashprasad2139]

Hi Ankit , PFB my logic and thanks a lot your video helped me a lot to build logic 😄. Do let me know if this is optimal or if it can be improved..
SELECT group_id,MIN(PLAYER_ID) PLAYER_ID FROM (
SELECT BB.*, P.group_id, MAX(TOTAL_PLAYER_SCORE) OVER (PARTITION BY group_id) SCORE_MAX_GROUP
FROM (
SELECT player_id, SUM(PLAYER_SCORE) TOTAL_PLAYER_SCORE
FROM (
SELECT first_player PLAYER_ID, first_score PLAYER_SCORE
FROM matches
UNION ALL
SELECT second_player PLAYER_ID, second_score PLAYER_SCORE
FROM matches
) AA
GROUP BY player_id
) BB
JOIN PLAYERS P ON P.player_id = BB.PLAYER_ID
)CC
WHERE TOTAL_PLAYER_SCORE=SCORE_MAX_GROUP
GROUP BY group_id

[@AnkitGupta-tp3ln]

your solution is already optimized, there is just one thing, your query won't give the score of the player