sqladvanced15 minutes

Fix Bug in SQL Query Aggregating Monthly Sales with Incorrect Joins

You are provided with a buggy SQL query that attempts to calculate the total monthly sales per product category. The query currently returns incorrect totals due to join and aggregation mistakes. Your task is to identify and fix these bugs so the query outputs the correct aggregated sales for each category per month.

Challenge prompt

The provided query is supposed to return total sales amount per product category grouped by month. However, it currently returns wrong totals because of incorrect JOIN conditions and aggregation placement. Fix the query so that it accurately sums sales amounts for each category by month. Tables: - sales(sale_id, product_id, sale_date, quantity, unit_price) - products(product_id, category_id, product_name) - categories(category_id, category_name) Fix the bugs in the query below: SELECT c.category_name, EXTRACT(YEAR FROM s.sale_date) AS sale_year, EXTRACT(MONTH FROM s.sale_date) AS sale_month, SUM(s.quantity) * s.unit_price AS total_sales FROM sales s LEFT JOIN products p ON s.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id GROUP BY c.category_name, sale_year, sale_month ORDER BY sale_year, sale_month, c.category_name;

Guidance

• Check the JOIN conditions and ensure they correctly link the tables without causing multiplication of rows.
• Review the aggregation logic, especially how the total sales calculation is written within the SUM function.
• Make sure the GROUP BY clause includes all non-aggregated selected columns or expressions.

Hints

• Multiply quantity and unit_price before applying the SUM, not after.
• Use INNER JOIN consistently if you want to exclude records without matching products or categories.
• Alias the EXTRACT expressions in SELECT and use those aliases in GROUP BY for clarity.

Starter code

SELECT
  c.category_name,
  EXTRACT(YEAR FROM s.sale_date) AS sale_year,
  EXTRACT(MONTH FROM s.sale_date) AS sale_month,
  SUM(s.quantity) * s.unit_price AS total_sales
FROM sales s
LEFT JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_name, sale_year, sale_month
ORDER BY sale_year, sale_month, c.category_name;

Expected output

category_name | sale_year | sale_month | total_sales --------------------------------------------------- Electronics | 2023 | 5 | 12500.00 Home & Garden | 2023 | 5 | 8400.50 Toys | 2023 | 5 | 4700.00 ... (and so on for each category and month)

Core concepts

SQL JOINsAggregation functionsGROUP BY usageQuery debugging

Challenge a Friend

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