DEA-C01 Question 124
Single answerYou are working as a data engineer for a company that processes large volumes of e-commerce transactions. The transaction data is stored in an Amazon Redshift table named 'transactions', and another table named 'products' contains product details. You need to write an SQL query to calculate the total sales (quantity * price) for each product category from these tables. Which of the following queries will correctly perform this calculation?
- A
SELECT p.category, SUM(t.quantity * p.price) AS total_sales FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY p.category
- B
SELECT category, SUM(quantity * price) AS total_sales FROM transactions JOIN products USING (product_id) GROUP BY category
- C
SELECT p.category, SUM(p.price) AS total_sales FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY p.category
- D
SELECT p.category, SUM(t.quantity * t.price) AS total_sales FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY p.category
Show answer and explanation
Correct answer: A
Explanation
The first query correctly joins the two tables using the product_id field and calculates the total sales by multiplying the quantity from the 'transactions' table with the price from the 'products' table. It also groups the results by the product category, which matches the requirements of the scenario. Proper use of table aliases avoids ambiguity and ensures the query adheres to best practices.
- A. Correct.
This query is correct. It correctly joins the 'transactions' and 'products' tables using the product_id and calculates total sales by multiplying the quantity from the 'transactions' table with the price from the 'products' table. The results are grouped by the product category.
- B. Incorrect.
This query is also correct in terms of the logic but does not specify table aliases, which can lead to ambiguity in complex queries. While functional, it's less precise and not a recommended best practice in a certification context.
- C. Incorrect.
This query is incorrect because it calculates the total price for each category but does not consider the quantity from the 'transactions' table, leading to inaccurate total sales calculations.
- D. Incorrect.
This query is incorrect because it multiplies the quantity and price from the same table ('transactions'), which results in an error since 'transactions' does not contain price information.