How To Solve LeetCode 586

Source: Dev.to

Problem Overview

The task is to identify the customer number that has placed the largest quantity of orders. The Orders table contains two identifier columns: order_number and customer_number. The test cases guarantee that there is exactly one customer with the most orders, but a follow‑up question asks how to handle ties.

Solution Approach

1. Aggregate orders per customer – Use COUNT() with GROUP BY customer_number to compute the number of orders for each customer.
2. Rank the aggregated results – Apply the window function RANK() ordered by the order count in descending order. This assigns a rank of 1 to the highest count and also to any customers that tie for the maximum.
3. Select the top‑ranked customer(s) – Filter the ranked result set for rows where the rank equals 1, returning the corresponding customer_number.

Using Common Table Expressions (CTEs) keeps the steps modular and readable.

SQL Solution (PostgreSQL)

WITH customer_order_count AS (
    SELECT customer_number,
           COUNT(order_number) AS order_count
    FROM Orders
    GROUP BY customer_number
),
customer_order_rank AS (
    SELECT 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;