sqlintermediate10 minutes

Refactor SQL Query for Sales Aggregation Optimization

Improve the given SQL query that calculates total and average sales per product category. The original query uses multiple subqueries and redundant joins, causing inefficiencies. Refactor it to be cleaner and more performant while preserving the exact results.

Challenge prompt

You are given a query that computes total and average sales amount for each product category by joining orders, order_items, and products tables. The original query uses nested subqueries and redundant joins, making it hard to maintain and inefficient. Your task is to refactor the query to optimize it without changing its behavior or output. Tables: - orders(order_id, order_date) - order_items(order_item_id, order_id, product_id, quantity, price) - products(product_id, category) Original query: SELECT category, (SELECT SUM(oi.quantity * oi.price) FROM order_items oi JOIN orders o ON oi.order_id = o.order_id JOIN products p2 ON oi.product_id = p2.product_id WHERE p2.category = p.category) AS total_sales, (SELECT AVG(oi.quantity * oi.price) FROM order_items oi JOIN products p3 ON oi.product_id = p3.product_id WHERE p3.category = p.category) AS avg_sales FROM products p GROUP BY category;

Guidance

• Focus on reducing the use of correlated subqueries by leveraging JOINs and GROUP BY clauses.
• Use aggregation functions directly in a single query rather than nested subqueries.
• Ensure that the refactored query produces the same output with category, total_sales, and avg_sales columns.

Hints

• Try starting with joining order_items to products first, then group by category.
• Calculate total and average sales directly in the SELECT clause using SUM and AVG.
• Avoid joining the orders table if it doesn't add filtering or aggregation benefits.

Starter code

SELECT category,
       (SELECT SUM(oi.quantity * oi.price)
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        JOIN products p2 ON oi.product_id = p2.product_id
        WHERE p2.category = p.category) AS total_sales,
       (SELECT AVG(oi.quantity * oi.price)
        FROM order_items oi
        JOIN products p3 ON oi.product_id = p3.product_id
        WHERE p3.category = p.category) AS avg_sales
FROM products p
GROUP BY category;

Expected output

category | total_sales | avg_sales ---------------------------------- Books | 15000 | 75.0 Clothing | 23000 | 115.0 Toys | 12000 | 60.0

Core concepts

SQL joinsaggregate functionsquery optimizationsubquery refactoring

Challenge a Friend

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