sqladvanced15 minutes

Fix Bug in Complex SQL Query Summarizing Sales Data

Debug and fix the provided SQL query that intends to summarize monthly sales data by product category but produces incorrect results due to logical errors and misused joins.

Challenge prompt

You are given a SQL query designed to calculate the total sales and average sales per product category per month from sales and product tables. However, the query currently returns incorrect totals, duplicates rows, or omits some data. Identify and fix the bugs in the SQL query so that it correctly aggregates total and average sales grouped by category and month. The schema consists of: - sales(sale_id INT, product_id INT, sale_date DATE, sale_amount DECIMAL) - products(product_id INT, category VARCHAR) Your fixed query must return columns: category, month (formatted as 'YYYY-MM'), total_sales, avg_sales_per_product Carefully review joins and grouping logic to ensure accuracy.

Guidance

• Check if joins between sales and products tables are causing unintended row multiplication or missing rows.
• Validate the GROUP BY clause includes all non-aggregated columns to ensure correct aggregation.
• Pay attention to the grouping by month: use proper date function to extract month in 'YYYY-MM' format.

Hints

• INNER JOIN might exclude categories with zero sales; consider if that's expected.
• Aggregates must be calculated per category and month, so group accordingly and extract month from sale_date with DATE_FORMAT or TO_CHAR depending on your SQL dialect.
• Avoid joining tables multiple times or creating cartesian products.

Starter code

SELECT p.category,
       DATE_FORMAT(s.sale_date, '%Y-%m') AS month,
       SUM(s.sale_amount) AS total_sales,
       AVG(s.sale_amount) AS avg_sales_per_product
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category;

Expected output

category | month | total_sales | avg_sales_per_product ---------|--------|-------------|----------------------- Books | 2024-04| 10500.00 | 350.00 Electronics | 2024-04 | 25000.00 | 1250.00 Clothing | 2024-04| 7000.00 | 233.33

Core concepts

SQL JoinsAggregation and GROUP BYDate Formatting in SQL

Challenge a Friend

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