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!
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)
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
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
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
![KSI - Dirty [Official Visualiser]](http://i.ytimg.com/vi/VoDh1h1dPUE/mqdefault.jpg)
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!
Thank you Florin for such kind words. I’m glad these videos are helping some people.
@@EverydayDataScience A lot.Thank you
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)
yeah just instead of count=first value, rank=1 wala filter aaega last select query mai
Very very helpful! Could you please tell when SQL medium questions will have more uploads?
Hey Rinka, it's coming tomorrow. Happy querying!
Please solve Hoppers Company questions on leetcode sql
Could we have used max window function in the cte2?
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
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
Can anyone explain why we used LEFT JOIN to join cte2 and products?
because we want to keep all the customer_id from the cte2.
55
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