LeetCode Medium 1783 Interview SQL Question with Detailed Explanation | Practice SQL

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[prateekmishra]

Great content bro! learning a lot through your videos . I really appreciate your effort :). Thanks again

[mickyman]

Really loved the apporach, Earlier the pivot table question used sum(case when condition....) to make column, but this appraoch should also will click to me now when I see a pivot table question. i.e. to use union

[kiranshah]

Man you are doing extremely great work 👏
Love you too much bro ❤

[expandingourselves]

select p_id as player_id, player_name, sum(wins) as grand_slams_count from
(select wimbledon as p_id, count(*) as wins from Championships
group by 1
union all
select Fr_open as p_id, count(*) as wins from Championships
group by 1
union all
select US_open as p_id, count(*) as wins from Championships
group by 1
union all
select Au_open as p_id, count(*) as wins from Championships
group by 1
) X
inner join Players Y on X.p_id = Y.player_id
group by 1, 2

A simpler method :)

[saisushanthdurvasulabitsian]

thank you so much for all the videos, here is my solution to the above problem, i have tried to use MR (Map Reduce) way of generating key value pairs in the first cte and reducing them in second cte, pls review and let me know your thoughts, thanks:

with tournament_player_details as
(
select Wimbledon as player_id
, 1 as entry
from Championships
union all
select Fr_open as player_id
, 1 as entry
from Championships
union all
select US_open as player_id
, 1 as entry
from Championships
union all
select Au_open as player_id
, 1 as entry
from Championships
), tournaments_won_per_player as
(
select player_id
, count(entry) as tournaments_won
from tournament_player_details
group by player_id
)
select twpp.player_id
, p.player_name
, twpp.tournaments_won as "grand_slams_count"
from tournaments_won_per_player twpp, Players p
where twpp.player_id = p.player_id

[ritusantra]

with player as (select wimbledon as player_id from grandslams
union all
select Fr_open as player_id from grandslams
union all
select US_open as player_id from grandslams
union all
select Au_open as player_id from grandslams)
select p.player_id, n.player_name, count(*) as grand_slams_count
from player p
inner join players n
on p.player_id = n.player_id
group by p.player_id, n.player_name;

[roymou]

with cte as
(
select year, "Wimbledon" as Championship, Wimbledon as player_id
from Championships
union
select year, "Fr_open" as Championship, Fr_open as player_id
from Championships
union
select year, "US_open" as Championship, US_open as player_id
from Championships
union
select year, "Au_open" as Championship, Au_open as player_id
from Championships
)

select p.player_id, p.player_name, count(c.player_id) as grand_slams_count
from cte c
join Players p
on c.player_id = p.player_id
group by player_id
order by player_id desc