LeetCode Medium 1158 Interview SQL Question with Detailed Explanation

Question: leetcode.com/p...
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

[@amywang1840]

Hi, thank you for sharing the content. But I have a question. why do we use 'cte' statement? I add user_id directly in the select statement, it works.

[@manikantaananthoja]

Hello bro, I solved this in different way
with joined_table as(
select user_id,
join_date,
order_id,
order_date,
buyer_id
from Users u left join Orders o
on u.user_id = o.buyer_id
and year(order_date) = 2019
)
select user_id as buyer_id,
join_date,
ifnull(count(order_id),0) as orders_in_2019
from joined_table
group by user_id,join_date;
Is this efficient only right?

[@ARkhan-xw8ud]

nice

[@kian69topgun1]

brilliant videos and thanks ! Please do sql problems of Stratascratch as well .

[@EverydayDataScience]

Glad that you are finding the videos useful. I have already uploaded about 40 questions from Stratascratch solving questions using both SQL and Python. You can find it on my channel playlist.

[@xiangyuanli1849]

Hi~ Can I ask why you don't insert join_date directly?

[@kavyatripathi2151]

Can you solve this series of hard sql questions on leetcode : 1635. Hopper Company Queries I | Hard |

[@ganeshg8457]

Actually there is no need of using it as cte instead we can just write join_date extra column.
As
Select u.user_id as buyer_id, join_date, sum(case when year(o.order_date)=2019 then 1 else 0 end) as orders_in_2019
From users u
Left join
Orders o
On u.user_id = o.buyer_id
Group by u.user_id

[@akshayrawat4528]

why u use left join in this......why not inner join?

[@vaibhavverma1340]

Hi sir, can youplease tell me why my query throw error
select buyer_id, join_date, sum(case when datepart(year, order_date) = '2019' then 1 else 0 end)orders_in_2019 from users u
left join orders o on u.user_id = o.buyer_id
group by buyer_id, join_date

[@june17you]

Here is my working query:
select user_id as buyer_id, join_date, count(order_id) as orders_in_2019
from users u
left join
(select order_id, buyer_id
from orders
where year(order_date) = '2019') o
on u.user_id = o.buyer_id
group by user_id;

[@shashikantshevale4544]

Instead of case statement and CTE ,we can simply used below query.
SELECT u.user_id as buyer_id,
u.join_date,
COUNT(o.order_id) AS orders_in_2019
FROM Users u
LEFT JOIN Orders o
ON u.user_id = o.buyer_id
AND YEAR(o.order_Date) = 2019
GROUP BY u.user_id,
u.join_date