sqlintermediate10 minutes

Fix the Incorrect JOIN Conditions in SQL Sales Report Query

This challenge presents a SQL query intended to generate a monthly sales summary by joining the sales and products tables. However, the query contains incorrect join logic that leads to incorrect results or excessive rows. Your task is to fix the join conditions and any other issues to produce an accurate sales report.

Challenge prompt

You have two tables: sales(sale_id, product_id, quantity_sold, sale_date) and products(product_id, product_name, price). The following SQL query attempts to generate a sales report showing total quantity sold and total revenue per product for March 2024. However, the query returns incorrect or inflated results due to bugs. Identify and fix all bugs in the query to ensure correct totals. Query: SELECT p.product_name, SUM(s.quantity_sold) as total_quantity, SUM(s.quantity_sold * p.price) as total_revenue FROM sales s JOIN products p ON s.sale_id = p.product_id WHERE s.sale_date BETWEEN '2024-03-01' AND '2024-03-31' GROUP BY p.product_name ORDER BY total_revenue DESC;

Guidance

• Confirm that join conditions use correct keys linking sales to products.
• Check date filter formats and ranges to ensure proper filtering.
• Validate aggregation logic to correctly calculate total quantities and revenue.

Hints

• The join currently links sale_id with product_id - are these related columns?
• Sale dates should be filtered inclusively for the entire month of March 2024.
• Aggregations should sum quantity_sold and multiplication by price to get revenue.

Starter code

SELECT 
  p.product_name,
  SUM(s.quantity_sold) as total_quantity,
  SUM(s.quantity_sold * p.price) as total_revenue
FROM sales s
JOIN products p ON s.sale_id = p.product_id
WHERE s.sale_date BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY p.product_name
ORDER BY total_revenue DESC;

Expected output

An aggregated list showing each product sold in March 2024, with correct total_quantity and total_revenue, ordered from highest to lowest revenue.

Core concepts

SQL JOIN conditionsSQL AggregationDate Filtering in SQL

Challenge a Friend

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