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 !

[@mahaksingg]

u r doing a great job thanks

[@ayushtyagi4240]

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

[@mlvprasadofficial]

8TH ONE COMPLETED..TQ

[@EverydayDataScience]

Awesome work, keep it up.

[@AbhishekSingh-vl1dp]

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

[@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

[@RahulSharma-ht2xz]

thank you so much sir

[@TheEleetcoder]

excellent!!

[@EverydayDataScience]

Glad that you found it useful.

[@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;

[@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)

[@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?

[@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);

[@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 )

[@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 );

[@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