Level 3 - Question 1: Multi-Table JOIN with Aggregation

Scenario

You work for an e-commerce company. Management wants to see total sales revenue per customer along with customer details.

Task

Write a query using INNER JOIN to display customer name, email, and their total order amount. Only include customers who have placed orders.

Sample Schema: `customers`

customer_id	name	email
1	Alice Johnson	alice@example.com
2	Bob Smith	bob@example.com
3	Carol White	carol@example.com

Sample Schema: `orders`

order_id	customer_id	amount
101	1	250.00
102	1	150.00
103	2	300.00

Solution:

SELECT c.name, c.email, SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name, c.email
ORDER BY total_spent DESC;

This query joins the customers and orders tables, calculates total spending per customer using SUM and GROUP BY, and orders results by total spent in descending order.

Back to Practice Hub Next Question