Query to List Customers Who Placed the Highest Number of Orders

This explanation will cover:

1. The database structure with dummy data
2. The SQL query to find customers with highest orders
3. Expected output
4. Step-by-step explanation of the query

Database Structure with Dummy Data

Let's create two tables for this scenario:

1. Customers table:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO Customers VALUES
(1, 'John Smith', 'john@example.com'),
(2, 'Jane Doe', 'jane@example.com'),
(3, 'Robert Johnson', 'robert@example.com'),
(4, 'Emily Davis', 'emily@example.com'),
(5, 'Michael Brown', 'michael@example.com');

2. Orders table:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Orders VALUES
(101, 1, '2023-01-15', 150.00),
(102, 1, '2023-02-20', 225.50),
(103, 1, '2023-03-10', 75.25),
(104, 2, '2023-01-05', 300.00),
(105, 2, '2023-04-18', 125.75),
(106, 3, '2023-05-22', 200.00),
(107, 3, '2023-06-30', 350.50),
(108, 3, '2023-07-12', 180.25),
(109, 3, '2023-08-05', 90.00),
(110, 4, '2023-09-10', 275.00),
(111, 5, '2023-10-15', 150.00);

SQL Query Solution

Here's the query to find customers who placed the highest number of orders:

WITH OrderCounts AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS number_of_orders
    FROM 
        Customers c
    LEFT JOIN 
        Orders o ON c.customer_id = o.customer_id
    GROUP BY 
        c.customer_id, c.customer_name
)
SELECT 
    customer_id,
    customer_name,
    number_of_orders
FROM 
    OrderCounts
WHERE 
    number_of_orders = (SELECT MAX(number_of_orders) FROM OrderCounts)
ORDER BY 
    customer_name;

Expected Output

Based on our dummy data, the output would be:

customer_id | customer_name | number_of_orders
------------+---------------+------------------
3           | Robert Johnson| 4

Step-by-Step Explanation

1. Common Table Expression (CTE) - OrderCounts:

   • We create a temporary result set that joins the Customers and Orders tables
   • For each customer, we count how many orders they've placed
   • The LEFT JOIN ensures we include customers even if they haven't placed any orders
   • We group by customer_id and customer_name to get counts per customer

2. Main Query:

   • We select from our CTE (OrderCounts)
   • The WHERE clause filters to only include customers whose order count matches the maximum order count found in the CTE
   • The subquery (SELECT MAX(number_of_orders) FROM OrderCounts) finds the highest order count
   • We sort the results by customer_name (though with only one customer in this case)

3. Why this works:

   • The CTE first calculates order counts for all customers
   • Then we compare each customer's count against the maximum count
   • This approach handles ties properly - if multiple customers had 4 orders, they'd all appear

Alternative Solution (without CTE)

SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS number_of_orders
FROM 
    Customers c
JOIN 
    Orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name
HAVING 
    COUNT(o.order_id) = (
        SELECT COUNT(order_id) 
        FROM Orders 
        GROUP BY customer_id 
        ORDER BY COUNT(order_id) DESC 
        LIMIT 1
    )
ORDER BY 
    c.customer_name;

This alternative uses HAVING with a subquery that finds the maximum order count directly from the Orders table.