Refactor a Query to Find Top Customers Efficiently
Improve an existing SQL query that fetches the top 5 customers based on total purchase amount by refactoring it for better readability and performance.
Challenge prompt
You are given a SQL query that selects the top 5 customers from the 'orders' table based on their total purchase amounts. However, the current query is repetitive and uses inefficient expressions that can be simplified. Refactor the query to improve its readability and efficiency while keeping the output correct. The final query should return each customer's ID along with their total purchase amount, ordered from highest to lowest total, limited to the top 5.
Guidance
- • Use aggregation functions and group by customer_id to get total purchases per customer.
- • Avoid recalculating the same expression multiple times by using aliases.
- • Ensure the query remains simple and readable.
Hints
- • Use SUM() to calculate total purchases per customer.
- • Use ORDER BY with the alias for the aggregated column to sort results.
- • LIMIT is helpful to restrict the number of rows returned.
Starter code
SELECT customer_id, SUM(amount) AS total_purchase
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 5;Expected output
A list of 5 customer_id and total_purchase pairs showing the highest spenders.
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.