LeetCode Medium 1596 Amazon Interview SQL Question with Detailed Explanation

[@florincopaci6821]

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!

[@EverydayDataScience]

Thank you Florin for such kind words. I’m glad these videos are helping some people.

[@florincopaci6821]

@@EverydayDataScience A lot.Thank you

[@yashnikhare2210]

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)

[@mickyman753]

yeah just instead of count=first value, rank=1 wala filter aaega last select query mai

[@rinkali7654]

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

[@EverydayDataScience]

Hey Rinka, it's coming tomorrow. Happy querying!

[@kavyatripathi2151]

Please solve Hoppers Company questions on leetcode sql

[@harshilgandhi8415]

Could we have used max window function in the cte2?

[@EverydayDataScience]

Great catch, Harshil. Yes, we can use MAX as well. The cte2 code will look something like this: cte2 AS (SELECT *, MAX(num_ordered) OVER(PARTITION BY customer_id) AS most_frequent FROM cte

[@adityams1659]

yess, But if you think about it, the shown method is more efficient. Since you have already used *Order by num_ordered desc* the first value by default is the value we want. if we use Max on it again we are unnecessarily increasing computational complexity

[@lakshmivenkatavarun8406]

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

[@nitinmadan4009]

because we want to keep all the customer_id from the cte2.

[@mlvprasadofficial]

55

[@james-r685]

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