Create a Simple Customer Orders Report Using SQL
Build a SQL query that models a basic data scenario by joining customer and order data to produce a simple report showing total order count and total amount spent per customer.
Challenge prompt
You are given two tables: Customers and Orders. The Customers table contains customer_id and customer_name. The Orders table contains order_id, customer_id, and order_amount. Write a SQL query that returns each customer's name, the total number of orders they have placed, and the total amount they spent on orders. If a customer has no orders, their total orders and amount should show as zero.
Guidance
- • Use a LEFT JOIN to ensure all customers are included, even those without orders.
- • Use aggregate functions COUNT() and SUM() to calculate the total orders and total amount spent.
- • Group the results by customer_name to get the summary per customer.
Hints
- • Remember to handle NULL values for customers without any orders using COALESCE.
- • COUNT(order_id) counts only existing orders; it won't count NULL values after LEFT JOIN.
- • Grouping by customer_id is possible but use customer_name for better readability in the output.
Starter code
SELECT c.customer_name, COUNT(o.order_id) AS total_orders, COALESCE(SUM(o.order_amount), 0) AS total_amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;Expected output
customer_name | total_orders | total_amount ---------------|--------------|------------- Alice | 3 | 150.00 Bob | 0 | 0.00 Charlie | 2 | 80.00
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.