sqlbeginner10 minutes

Refactor a SQL Query to Use Simple Aggregation Instead of Subqueries

Improve the readability and performance of a SQL query that calculates total sales per customer by refactoring it from using multiple subqueries to a clean aggregation using GROUP BY.

Challenge prompt

You are given a query that calculates the total sales amount for each customer by using multiple correlated subqueries. Refactor this query to remove the subqueries and instead use a single GROUP BY clause with aggregation to achieve the same results more efficiently and clearly.

Guidance

• Understand the current logic and identify the repeated subqueries.
• Replace the subqueries with a simple JOIN and GROUP BY on the sales table.
• Test the refactored query to ensure it produces the same output.

Hints

• Think about how SUM() and GROUP BY can aggregate values per customer.
• Joining the customers and sales tables first helps to aggregate sales per customer easily.

Starter code

SELECT
  c.customer_id,
  c.customer_name,
  (SELECT SUM(sale_amount) FROM sales s WHERE s.customer_id = c.customer_id) AS total_sales
FROM customers c;

Expected output

A list of customers with their respective total sales summed up, matching the original query's output but achieved without subqueries.

Core concepts

SQL aggregationGROUP BY clauseJOIN operationsquery optimization

Challenge a Friend

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