Fix the Bug in SQL Query to Calculate Average Order Value Per Customer
You are given a broken SQL query that attempts to calculate the average order value for each customer from an orders table. The query returns incorrect results due to logical and syntactical bugs. Your task is to identify and fix these bugs so the query produces the correct average order value per customer.
Challenge prompt
The orders table contains columns: customer_id, order_id, order_amount. The goal is to write a query that outputs each customer_id alongside their average order amount (order_value). The broken query below produces incorrect averages and duplicates rows. Identify and fix the errors to ensure each customer appears once with their correct average order value.
Guidance
- • Check the use of aggregate functions and GROUP BY clause.
- • Verify that no unnecessary joins or duplicates appear in the result.
- • Test and validate the output with sample data to ensure accuracy.
Hints
- • Make sure the GROUP BY clause aligns correctly with the selected columns.
- • Review how AVG() is used and whether the total sum and count are being applied properly.
- • Look out for joins or subqueries that might be duplicating rows unintentionally.
Starter code
SELECT customer_id, SUM(order_amount) / COUNT(order_id) AS avg_order_value FROM orders;Expected output
customer_id | avg_order_value 1 | 150.75 2 | 220.00 3 | 310.50
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.