sqlbeginner10 minutes

Refactor a Repetitive SQL Query to Use Better Aggregation

Improve a basic SQL query by refactoring repetitive SELECT statements with better use of aggregation and GROUP BY to optimize the code while maintaining the original result.

Challenge prompt

You have a table named Sales with columns: region (text), product (text), and amount (integer). The current query selects total sales for each product but repeats almost the same SELECT statements for each product individually. Refactor the query to output the same result—a list of products and their total sales—using a single query without repetition.

Guidance

• Identify repetitive code segments that can be combined.
• Use GROUP BY and aggregation functions like SUM to simplify the query.

Hints

• Think about how GROUP BY works to aggregate multiple rows into summary results.
• Try to write a query that scans the table just once instead of multiple times.

Starter code

SELECT 'Apple' AS product, SUM(amount) AS total_sales FROM Sales WHERE product = 'Apple'
UNION ALL
SELECT 'Banana' AS product, SUM(amount) AS total_sales FROM Sales WHERE product = 'Banana'
UNION ALL
SELECT 'Cherry' AS product, SUM(amount) AS total_sales FROM Sales WHERE product = 'Cherry';

Expected output

product | total_sales --------|------------ Apple | 1000 Banana | 850 Cherry | 400

Core concepts

GROUP BYSUM()UNION ALLQuery Optimization

Challenge a Friend

Send this duel to someone else and see if they can solve it.