Customer Placing the Largest Number of Orders


  • 0

    Click here to see the full article post


  • 0
    Y

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


  • 0
    P

    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)


  • 0
    C

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


  • 0
    A

    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
    )
    

  • 0
    G

    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)
    

Log in to reply
 

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