Fix the Join Logic to Correctly Aggregate Sales by Customer
This challenge asks you to debug a broken SQL query intended to calculate total sales per customer by joining sales and customers tables. The existing query returns incorrect aggregates due to an erroneous join and grouping logic.
Challenge prompt
You are given two tables: `customers` and `sales`. - `customers` has columns: `customer_id`, `customer_name`. - `sales` has columns: `sale_id`, `customer_id`, `amount`. The current query aims to calculate the total sales amount per customer but returns incorrect results because of bugs in the join and grouping logic. Fix the query so that it correctly computes the total sales amount for each customer, including customers with zero sales. Here is the broken query: SELECT c.customer_name, SUM(s.amount) AS total_sales FROM customers c JOIN sales s ON c.customer_id = s.sale_id GROUP BY c.customer_id;
Guidance
- • Check the join condition between `customers` and `sales`, ensuring the correct keys are matched.
- • Use the appropriate JOIN type to include customers with no sales.
- • Ensure the GROUP BY clause contains the correct columns for aggregation.
Hints
- • The join condition currently matches customer_id to sale_id, which is incorrect.
- • Consider using a LEFT JOIN to include customers with no matching sales entries.
- • GROUP BY should match any non-aggregated columns in the SELECT statement.
Starter code
SELECT c.customer_name, SUM(s.amount) AS total_sales
FROM customers c
JOIN sales s ON c.customer_id = s.sale_id
GROUP BY c.customer_id;Expected output
A list of customer names with their corresponding total sales amounts. Customers without sales should appear with a total_sales amount of NULL or 0. Example: customer_name | total_sales --------------|------------ Alice | 1500 Bob | 0 Charlie | 230
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.