Objective

Write a SQL query that retrieves the names and email addresses of customers who have placed more than two orders. Additionally, for each customer, calculate the total number of orders they have placed and the total amount they have spent. The result should be sorted in descending order based on the total amount spent.

Additional Information

• The database consists of two tables: customers and orders.
• If a customer has not placed any orders, the total amount spent should be considered as 0.
• The output should include the following columns: customer_name, email, total_orders, and total_spent.
• The results should be sorted in descending order by total_spent.

The customers table:

The orders table:

SELECT
    c.customer_name,
    c.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name, c.email
HAVING COUNT(o.id) > 2
ORDER BY total_spent DESC;

Explanation

Step 1: Join customers with their orders

FROM customers c
JOIN orders o ON c.id = o.customer_id

An INNER JOIN combines each customer row with their matching order rows. A customer with 3 orders produces 3 joined rows. Customers with no orders are excluded entirely, which is fine here since we only want customers with more than 2 orders anyway.

Step 2: Group by customer to aggregate their orders

GROUP BY c.id, c.customer_name, c.email

GROUP BY collapses all rows for the same customer into a single row. We include c.id along with customer_name and email to ensure grouping is based on the unique identifier (two customers could share a name).

Step 3: Calculate totals with aggregate functions

COUNT(o.id) AS total_orders,
COALESCE(SUM(o.amount), 0) AS total_spent

COUNT(o.id) counts how many order rows exist for each customer. SUM(o.amount) adds up the order amounts. COALESCE(..., 0) ensures a 0 instead of NULL if there are no amounts.

Step 4: Filter with HAVING

HAVING COUNT(o.id) > 2

HAVING filters after aggregation, unlike WHERE which filters before. You cannot use WHERE with aggregate functions like COUNT or SUM. This keeps only customers who have placed more than 2 orders.

Step 5: Sort by total spent

ORDER BY total_spent DESC;

Lists the highest-spending customers first.

Alternative: Correlated Subqueries

This approach uses subqueries instead of JOIN + GROUP BY:

SELECT
    c.customer_name,
    c.email,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders,
    (SELECT COALESCE(SUM(amount), 0) FROM orders o WHERE o.customer_id = c.id) AS total_spent
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 2
ORDER BY total_spent DESC;

This works but is less efficient. For each customer, the database runs 3 separate scans of the orders table (two in SELECT, one in WHERE). The JOIN approach processes everything in a single pass.