LeetCode Medium 1596 Amazon Interview SQL Question with Detailed Explanation

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

[florincopaci]

it's impossible not to understand how you explain, I haven't seen anyone explain how you explained for example the problem with self join with managers up to level 3. You should be a trainer if you are not already. All the best!

[yashnikhare]

We can also use Rank Function cte2 as (select *, rank() over(partition by customer_id order by num_ordered desc ) as rnk from cte Then use where clause (where rnk=1)

[rinkali]

Very very helpful! Could you please tell when SQL medium questions will have more uploads?

[kavyatripathi]

Please solve Hoppers Company questions on leetcode sql

[harshilgandhi]

Could we have used max window function in the cte2?

[lakshmivenkatavarun]

Can anyone explain why we used LEFT JOIN to join cte2 and products?

[james-r]

another solution:
with cte1 as (
select customer_id, product_id, count(1) as ct from orders
group by customer_id, product_id
),

cte2 as (
select *, dense_rank() over(partition by customer_id order by ct desc) as dr from cte1
)

select a.customer_id, a.product_id, b.product_name from cte2 a
left join products b on a.product_id=b.product_id where dr=1