How To Solve LeetCode 586

This problem asks you to find the customer number that has placed the largest quantity of orders. The customer number is the identifier for the customer in the orders table as explained in the problem. My first intuition was to look at the desired output. According to the problem description, the test cases will only produce exactly one customer with the most orders. It is important to note that at the end of the problem there is a follow up question asking what if more than one customer has the largest number of orders.

With that said, let's break this problem down into smaller, modular pieces. The orders table simply contains two columns that are both identifiers, order number and customer number as previously stated. My main goal to solve this problem is to aggregate the data to get a count of orders per the respective customer id. In addition, I want to also use a window function to rank the order counts just in case there are multiple customers tied with the max order count.

CTE (Common Table Expression)

Thus, we can easily aggregate the data using the GROUP BY clause counting the number of orders per customer by using the aggregate function COUNT(). This aggregation will form our first Common Table Expression (CTE), which will be used in the next CTE to assign a rank using the entire result set as a partition.

In the next Common Table Expression, I use the RANK() function to rank the rows per a specific partition. The partition that I chose was the whole result set. Since I omitted the PARTITION BY keyword, the data is being partitioned by the whole result set. The result set is ordered in descending order by the order count column, so the count of the most orders will receive a ranking of 1. In the unlikely case that there is a tie with customers then the rank will assign a 1 to both customer rows. This safeguards the solution in case there is a tie for the maximum number of orders.

Conclusion

In the last step, I bring it all together by querying the customer_order_rank CTE and only returning the customer identifier for the rows where the rank is 1 in the SELECT clause. I solved this problem using the PostgreSQL dialect of SQL, below you can reference the syntax. It is important to note that the OVER keyword is used to tell the database engine what you are ranking, and in this scenario I am ranking the partition of the whole result set ordered by the order count descending.

WITH customer_order_count AS (
    SELECT customer_number AS customer_number, 
    COUNT(order_number) AS order_count
    FROM Orders
    GROUP BY customer_number
),
customer_order_rank AS (
    SELECT customer_number AS customer_number,
    RANK() OVER (ORDER BY order_count DESC) AS order_rnk
    FROM customer_order_count
)

SELECT customer_number
FROM customer_order_rank
WHERE order_rnk = 1;