sqlintermediate10 minutes

Refactor SQL Query for Sales Data Aggregation to Improve Performance

Optimize a given SQL query that aggregates sales data by product category and month, making it cleaner and more efficient while preserving its functionality.

Challenge prompt

You are provided with a SQL query that calculates total sales and average sales per month for each product category. The query currently uses multiple subqueries and redundant joins which degrade performance and readability. Refactor the query to use cleaner joins, reduce unnecessary subqueries, and improve its efficiency. Ensure the final output contains the product category, month (as YYYY-MM), total sales, and average monthly sales.

Guidance

• Analyze the query to identify redundant or repeated calculations and subqueries.
• Consider using common table expressions (CTEs) or derived tables to simplify the query logic.
• Ensure that groupings and joins are done efficiently to avoid scanning the same tables multiple times.

Hints

• Try consolidating all sales aggregations in a single aggregation step before joining with categories.
• Use DATE_FORMAT or similar functions carefully to avoid interfering with index usage if applicable.
• Check if the join conditions can be simplified or replaced with inner joins that provide the same result.

Starter code

SELECT
  c.category_name,
  DATE_FORMAT(s.sale_date, '%Y-%m') AS sale_month,
  (SELECT SUM(amount) FROM sales WHERE product_id IN (SELECT product_id FROM products WHERE category_id = c.category_id) AND DATE_FORMAT(sale_date, '%Y-%m') = DATE_FORMAT(s.sale_date, '%Y-%m')) AS total_sales,
  (SELECT AVG(monthly_sales) FROM (
    SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(amount) AS monthly_sales
    FROM sales
    WHERE product_id IN (SELECT product_id FROM products WHERE category_id = c.category_id)
    GROUP BY month
  ) AS monthly_data) AS avg_sales
FROM categories c
JOIN sales s ON s.product_id IN (SELECT product_id FROM products WHERE category_id = c.category_id)
GROUP BY c.category_name, sale_month;

Expected output

category_name | sale_month | total_sales | avg_sales --------------|------------|-------------|---------- Electronics | 2024-01 | 120000 | 10000 Electronics | 2024-02 | 80000 | 10000 Books | 2024-01 | 50000 | 8333 Books | 2024-02 | 10000 | 8333

Core concepts

SQL joinsAggregation functionsSubquery optimizationCommon Table Expressions

Challenge a Friend

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