DEA-C01 Question 333
Select 2You are managing a data pipeline for an e-commerce company, and the sales data is stored in an Amazon Aurora MySQL database. The Orders table contains customer orders, and the Products table contains product details. The schema for the tables is as follows:
Orders table:
order_id(Primary Key)customer_idproduct_idorder_datequantity
Products table:
product_id(Primary Key)product_nameprice
You need to generate a report that shows the customer_id, product_name, and the total revenue (quantity * price) for all orders placed in September 2023. Which SQL query will achieve this?
- A
SELECT customer_id, product_name, SUM(quantity * price) AS total_revenue FROM Orders INNER JOIN Products ON Orders.product_id = Products.product_id WHERE order_date >= '2023-09-01' AND order_date <= '2023-09-30' GROUP BY customer_id, product_name;
- B
SELECT customer_id, product_name, quantity * price AS total_revenue FROM Orders INNER JOIN Products ON Orders.product_id = Products.product_id WHERE MONTH(order_date) = 9 AND YEAR(order_date) = 2023;
- C
SELECT customer_id, product_name, SUM(quantity * price) AS total_revenue FROM Orders LEFT JOIN Products ON Orders.product_id = Products.product_id WHERE order_date BETWEEN '2023-09-01' AND '2023-09-30' GROUP BY customer_id;
- D
SELECT customer_id, product_name, SUM(quantity * price) AS total_revenue FROM Orders INNER JOIN Products ON Orders.product_id = Products.product_id WHERE order_date BETWEEN '2023-09-01' AND '2023-09-30' GROUP BY customer_id, product_name;
Show answer and explanation
Correct answers: A, D
Explanation
The two correct queries (Option 1 and Option 4) correctly join the Orders and Products tables, filter the data to include only orders from September 2023, calculate the total revenue for each order, and group the results by both customer_id and product_name as required by the report. These are the only queries that meet all the specified requirements for generating the report accurately.
- A. Correct.
This query correctly uses an INNER JOIN to combine the
OrdersandProductstables, filters by the date range for September 2023, and calculates the total revenue using SUM(quantity * price). It also groups the results bycustomer_idandproduct_name, which is required to generate the desired report. - B. Incorrect.
This query calculates the total revenue without using SUM(), which would result in incorrect aggregation for multiple orders of the same product. Additionally, it does not group by both
customer_idandproduct_name, leading to incorrect grouping. - C. Incorrect.
This query uses a LEFT JOIN instead of an INNER JOIN. Since all orders must have a corresponding product, a LEFT JOIN is unnecessary and could include irrelevant data. Additionally, it groups only by
customer_id, which misses the required grouping byproduct_name. - D. Correct.
This query is correct as it uses an INNER JOIN, filters by the September 2023 date range, calculates total revenue using SUM(quantity * price), and groups by both
customer_idandproduct_name, fulfilling all requirements.