Leetcode Hard problem 2| Tournament Winners | Complex SQL 8

In this video we will discuss a leetcode hard problem, Here is the script to create and insert data:

create table players
(player_id int,
group_id int)

insert into players values (15,1);
insert into players values (25,1);
insert into players values (30,1);
insert into players values (45,1);
insert into players values (10,2);
insert into players values (35,2);
insert into players values (50,2);
insert into players values (20,3);
insert into players values (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);
insert into matches values (2,30,25,1,2);
insert into matches values (3,30,15,2,0);
insert into matches values (4,40,20,5,2);
insert into matches values (5,35,50,1,1);

[abhishek_grd]

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

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

[akash]

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

[gauravgupta]

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

[ishamajumdar]

My Approach :
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.group_id, c.player, SUM(c.Score) AS total_score,
DENSE_RANK() OVER(PARTITION BY group_id ORDER BY SUM(c.Score) DESC, player ASC) AS rnk
FROM cte c
JOIN players p
ON c.player = p.player_id
GROUP BY p.group_id, c.player
)
SELECT group_id, player AS winner_player
FROM cte2
WHERE rnk = 1;

Thankyou soo much Ankit sir. Only because of your teaching, I solved this question even without seeing your solution and whole video. Thankyou again for teaching such complex topics in a super easy way. Now I can solve SQL questions slowly and also getting confidence. Please continue your SQL series sir.

[LS]

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

[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

[ramakumarguntamadugu]

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.

[anupamsharma]

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

[abb_raj]

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

[aaravkumarsingh]

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;

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

[hairavyadav]

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;

[arpiteshsrivastava]

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;

[mranaljadhav]

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;

[dfkgjdflkg]

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

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

[anishchhabra]

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;

[kartikeyasingh]

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 ;