sqlintermediate10 minutes

Fix Incorrect Join and Aggregation in Sales Report Query

A SQL query intended to generate a monthly sales report per product category uses incorrect join syntax and aggregation logic. Fix the query to produce accurate total sales per category per month.

Challenge prompt

You are given a broken SQL query that aims to report the total sales amount for each product category grouped by month. However, the results show incorrect totals and duplicate rows. Identify and fix the join and aggregation issues to return the correct monthly sales totals per category. The database schema includes the following tables: - sales(sale_id, product_id, sale_date, amount) - products(product_id, product_name, category_id) - categories(category_id, category_name) Broken Query: SELECT c.category_name, DATE_FORMAT(s.sale_date, '%Y-%m') AS sale_month, SUM(s.amount) AS total_sales FROM sales s JOIN products p, categories c ON s.product_id = p.product_id AND p.category_id = c.category_id GROUP BY c.category_name, sale_month ORDER BY sale_month, c.category_name; Fix the query so it properly joins the tables and correctly aggregates total sales per category by month.

Guidance

• Check the JOIN syntax to ensure all tables join correctly in a single JOIN clause tree.
• Verify that aggregation (SUM) and GROUP BY expressions correspond correctly to the selected columns.
• Test the query on sample data to confirm duplicate rows are eliminated and totals are accurate.

Hints

• Replace the misplaced commas in the FROM and JOIN clauses with explicit JOIN statements.
• Make sure each JOIN has the appropriate ON condition mapping keys between tables.
• GROUP BY all non-aggregated select expressions exactly as they appear.

Starter code

SELECT
  c.category_name,
  DATE_FORMAT(s.sale_date, '%Y-%m') AS sale_month,
  SUM(s.amount) AS total_sales
FROM
  sales s
JOIN
  products p,
  categories c
  ON s.product_id = p.product_id
  AND p.category_id = c.category_id
GROUP BY
  c.category_name, sale_month
ORDER BY
  sale_month, c.category_name;

Expected output

category_name | sale_month | total_sales ------------- | ---------- | ----------- Books | 2024-01 | 15000.00 Books | 2024-02 | 12000.00 Electronics | 2024-01 | 35000.00 Electronics | 2024-02 | 42000.00

Core concepts

SQL JOINGROUP BY and AggregationsDate Formatting

Challenge a Friend

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