Fix Bug in SQL Query for Customer Lifetime Value Calculation
Identify and fix the bug in the provided SQL query that attempts to calculate the Customer Lifetime Value (CLV) by aggregating total purchases over all transactions per customer, but currently returns incorrect results due to improper GROUP BY and JOIN logic.
Challenge prompt
The given query tries to calculate each customer's lifetime value by summing their total purchase amounts from the transactions table joined with customer info. However, the output has duplicate rows and incorrect sums. Your task is to find and fix the bug in the SQL query so the results correctly show each customer's total lifetime value, with one row per customer.
Guidance
- • Review the JOIN conditions and ensure that the grouping is done properly at the customer level.
- • Check if aggregations are performed after joining or before to avoid multiplication of rows.
- • Make sure all selected columns comply with GROUP BY clauses.
Hints
- • The INNER JOIN might cause row multiplication if multiple transactions match a single customer. Try aggregating before joining.
- • GROUP BY should include all non-aggregated columns in the SELECT.
- • Consider using subqueries or Common Table Expressions (CTEs) for aggregation before joining.
Starter code
SELECT c.customer_id, c.customer_name, SUM(t.purchase_amount) AS lifetime_value
FROM customers c
INNER JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_id;
Expected output
customer_id | customer_name | lifetime_value ------------|---------------|---------------- 1 | Alice | 1500.00 2 | Bob | 900.50 3 | Charlie | 1230.75
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.