sqlintermediate10 minutes

Refactor Complex Sales Report Query for Performance Optimization

Improve a verbose and inefficient SQL query that generates a monthly sales report by refactoring it into a cleaner, optimized form while maintaining its original behavior.

Challenge prompt

You are given a SQL query that produces a monthly sales report including total sales, number of orders, and average order value per customer. The current query uses multiple nested subqueries and repetitive calculations, making it hard to maintain and slow to execute. Your task is to refactor this query into a more readable and efficient form, using appropriate JOINs, common table expressions (CTEs), and aggregation techniques, without changing the output results.

Guidance

• Identify and eliminate redundant calculations or repeated subqueries by consolidating them.
• Consider using CTEs to break down the query into logical parts for clarity and reusability.

Hints

• Look for opportunities to replace subqueries in SELECT statements with JOINs or window functions.
• Check if aggregations can be computed in a single pass rather than multiple separate steps.

Starter code

SELECT
  customer_id,
  (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)) AS total_orders,
  (SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)) AS total_sales,
  (SELECT AVG(amount) FROM orders WHERE customer_id = c.customer_id AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)) AS avg_order_value
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', CURRENT_DATE))
ORDER BY total_sales DESC;

Expected output

A result set with columns: customer_id, total_orders, total_sales, avg_order_value for all customers who placed orders in the current month, sorted by total_sales descending.

Core concepts

SQL optimizationCommon Table Expressions (CTEs)Joins and aggregation

Challenge a Friend

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