Customer Placing the Largest Number of Orders

• How would you update this to handle the case where there could be more than one numbers occurring the same number of maximum times?

• I think this can handle more than one numbers occurring the same number of max times:
SELECT customer_number FROM orders
GROUP BY customer_number
having count(distinct order_number) IN
(SELECT MAX(countn) FROM (select count(distinct order_number) as countn FROM orders GROUP BY customer_number) sub)

• select customer_number
from orders
group by customer_number
having count(*) >1

• It can handle the case where more than one customer can have maximum number of orders.

``````SELECT customer_number
FROM orders
GROUP BY customer_number
HAVING count(order_number) = (
SELECT count(order_number)
FROM orders
GROUP BY customer_number
ORDER BY count(order_number) DESC LIMIT 1
)
``````

• Follow up: What if more than one customer have the largest number of orders, can you find all the customer_number in this case?

Do not use triangle join, because it is bad scalibility and performance

Step:

1. Find the number of the largest count on order number
2. Select the customer who has same number of order as the largest count on order number
``````SELECT customer_number
FROM orders
Group BY customer_number
HAVING  count(order_number) =
(SELECT max(numOfOrder)
FROM
(SELECT customer_number,count(order_number) as numOfOrder
FROM orders
Group By customer_number) as temp)
``````

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.