LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 586

[@deepanshudutta4443]

Sir awasome... everyday i solve problems by taking help from your videos

[@EverydayDataScience]

Keep it up, Deepanshu. Consistency is the key !

[@ayushtyagi4240]

select customer_number from Orders group by customer_number order by count(customer_number) desc limit 1 this is also one way

[@AbhishekSingh-vl1dp]

select customer_number from Orders group by customer_number order by count(order_number) desc limit 1 this is much simple

[@mahaksingg]

u r doing a great job thanks

[@rayyanamir8560]

with cte as ( select*,count(order_number) over (partition by customer_number) as count_of_order from orders ) ,cte2 as ( select*, dense_rank() over(order by count_of_order desc) as rank_of_orders from cte ) select customer_number from cte2 where rank_of_orders=1 limit 1

[@luyaowang4606]

hhh, not sure if this one is faster

[@abhisheksharma6617]

May not be the best approach, but works : select customer_number from orders group by customer_number having count(*) > 1 order by count(*) desc limit 1;

[@mlvprasadofficial]

8TH ONE COMPLETED..TQ

[@EverydayDataScience]

Awesome work, keep it up.

[@RahulSharma-ht2xz]

thank you so much sir

[@pankajchandel1000]

with cte as (select count(order_number) as NumOdr from Orders group by customer_number) select customer_number from cte where NumOdr = (select max(NumOdr) from cte);

[@RitzFootball]

Learned new concept of CTE, nicely explained, 1 doubt that is why are we mentioning FROM cte again in the end . u have already mentioned FROM cte in the 9th line.can u pls explain this. thank you.

[@EverydayDataScience]

Okay, so you can't write WHERE NumOrd = MAX(NumOrd). You need to calculate the MAX of that column. So firstly the subquery, SELECT MAX(NumOrd) FROM cte executes and finds the maximum value. Then the cursor goes to outer part i.e. FROM cte it SELECTs customer_number and filters only those rows where NumOrd is equal to Maximum (WHERE NumOrd = the maximum value calculated from the subquery)

[@pankajchandel1000]

select customer_number,count(order_number) from Orders group by customer_number having count(order_number)= select max(NumOdr) from(select count(order_number) as NumOdr from Orders group by customer_number )

[@pranveshpandey6316]

Hi, can you please help with this query which i have written, it passes the testcase wnat to know if it is more efficient than yours? select t.customer_number from (select customer_number, count(customer_number) as countcc from Orders group by customer_number order by countcc desc limit 1) as t

[@MyAnish22]

No semi colon required for cte queries?

[@s.211]

select customer_number from orders group by customer_number having count(customer_number) >= all(select count(customer_number) from orders group by customer_number );

[@TheEleetcoder]

excellent!!

[@EverydayDataScience]

Glad that you found it useful.

[@arunraj3866]

Select customer _number from Orders groupby customer_number Having Count(customer_number) >1 Will this work?

[@hemanthtummala753]

instead we can use limit 0,1

[@dynamickaushik2568]

The query in this video is correct. 👍 I tried other solution which is as follows SELECT MAX(customer_number) AS customer_number FROM Orders This query is accepted by Test Result. However, when I click the Submit button it shows Wrong answer. Later on I read the whole question again, then I found at the very end there is "Follow Up" information that also must be considered. So, that's why the query in this video is correct.

[@anirbansarkar6306]

with cte as( select customer_number, count(*) as total_orders from Orders group by customer_number ), cte2 as( select customer_number, dense_rank() over(order by total_orders desc) as ranku from cte ) select customer_number from cte2 where ranku=1