Solution:Step 1: Use LEFT JOINs to include all customersLEFT JOIN orders and payments to include customers without orders/payments.Step 2: Aggregate latest order date and total paymentsUse MAX(o.date) for latest order and SUM(p.amount) for total payments, grouping by customer name.Final Answer:SELEC

[Solved] You have tables:customers(id, name), orders(id, customer_id, date), payments(id, order_id, amount)Write a query to list all customers with their latest order date and total payment amount, including... | SQL

SQL - LEFT and RIGHT JOIN

You have tables:
customers(id, name), orders(id, customer_id, date), payments(id, order_id, amount)
Write a query to list all customers with their latest order date and total payment amount, including customers with no orders or payments.

ASELECT c.name, o.date, p.amount FROM customers c LEFT JOIN orders o ON c.id = o.customer_id INNER JOIN payments p ON o.id = p.order_id;

BSELECT c.name, o.date, p.amount FROM customers c INNER JOIN orders o ON c.id = o.customer_id INNER JOIN payments p ON o.id = p.order_id;

CSELECT c.name, MAX(o.date) AS latest_order, SUM(p.amount) AS total_payment FROM customers c LEFT JOIN orders o ON c.id = o.customer_id LEFT JOIN payments p ON o.id = p.order_id GROUP BY c.name;

DSELECT c.name, MAX(o.date), SUM(p.amount) FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id LEFT JOIN payments p ON o.id = p.order_id GROUP BY c.name;

Step-by-Step Solution

Solution:

Step 1: Use LEFT JOINs to include all customers
LEFT JOIN orders and payments to include customers without orders/payments.
Step 2: Aggregate latest order date and total payments
Use MAX(o.date) for latest order and SUM(p.amount) for total payments, grouping by customer name.
Final Answer:
SELECT c.name, MAX(o.date) AS latest_order, SUM(p.amount) AS total_payment FROM customers c LEFT JOIN orders o ON c.id = o.customer_id LEFT JOIN payments p ON o.id = p.order_id GROUP BY c.name; -> Option C
Quick Check:
LEFT JOIN + aggregation for all customers = SELECT c.name, MAX(o.date) AS latest_order, SUM(p.amount) AS total_payment FROM customers c LEFT JOIN orders o ON c.id = o.customer_id LEFT JOIN payments p ON o.id = p.order_id GROUP BY c.name; [OK]

Quick Trick: Use LEFT JOIN and GROUP BY to include all main table rows [OK]

Common Mistakes:

MISTAKES

Using INNER JOIN excludes customers without orders
Mixing JOIN types incorrectly
Missing GROUP BY with aggregates

Master "LEFT and RIGHT JOIN" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You have tables:

Step 1: Use LEFT JOINs to include all customers

Step 2: Aggregate latest order date and total payments

Final Answer:

Quick Check:

Want More Practice?