sqlbeginner10 minutes

Create a Simple Customer Orders Summary View

Build a SQL query to create a summary view that lists each customer alongside the total number of orders they have placed and the total amount spent.

Challenge prompt

You have two tables: Customers and Orders. The Customers table contains customer_id, customer_name, and contact_info. The Orders table contains order_id, customer_id, order_date, and order_amount. Write a SQL query to create a view called CustomerOrderSummary that displays customer_id, customer_name, total_orders (count of orders), and total_spent (sum of order_amount) for each customer. Include customers even if they have placed zero orders (show total_orders as 0 and total_spent as 0 in such cases).

Guidance

• Use a LEFT JOIN from Customers to Orders to include all customers.
• Use aggregate functions COUNT and SUM to calculate total orders and spending.
• Use COALESCE to handle NULL values for customers with no orders.
• Group by customer_id and customer_name to aggregate data correctly.

Hints

• Remember that COUNT(column) does not count NULL values, but COUNT(*) counts rows.
• COALESCE(expression, 0) can replace NULL with 0 in your SELECT statement.
• LEFT JOIN ensures all customers are included regardless of orders.

Starter code

CREATE VIEW CustomerOrderSummary AS
SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS total_orders,
  SUM(o.order_amount) AS total_spent
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Expected output

A view named CustomerOrderSummary with columns: customer_id, customer_name, total_orders, total_spent where each row corresponds to a unique customer and aggregates their order count and total spending, showing zeroes if no orders exist.

Core concepts

LEFT JOINAGGREGATE FUNCTIONSGROUP BYCOALESCE

Challenge a Friend

Send this duel to someone else and see if they can solve it.