DEA-C01 Question 121
Single answerYou are working as a data engineer at a company that uses Amazon Redshift for their data warehouse. You are tasked with transforming a table named 'sales' to calculate the total revenue for each product category. The 'sales' table has the following columns: 'sale_id', 'product_id', 'category', 'quantity', and 'price'. Which SQL query will correctly calculate the total revenue (quantity * price) for each product category?
- A
SELECT category, SUM(quantity * price) AS total_revenue FROM sales GROUP BY category;
- B
SELECT category, quantity * price AS total_revenue FROM sales GROUP BY category;
- C
SELECT category, SUM(quantity + price) AS total_revenue FROM sales GROUP BY category;
- D
SELECT category, AVG(quantity * price) AS total_revenue FROM sales GROUP BY category;
Show answer and explanation
Correct answer: A
Explanation
The task requires calculating the total revenue for each product category. Total revenue is calculated by multiplying 'quantity' and 'price' for each row and then summing up these values per category. The correct SQL query uses SUM(quantity * price) and GROUP BY category to achieve this transformation.
- A. Correct.
This is the correct query as it calculates the total revenue by multiplying 'quantity' and 'price' for each row, then summing the results for each 'category'. The GROUP BY clause ensures that the aggregation is done per category.
- B. Incorrect.
This query is incorrect because 'quantity * price' is not aggregated with a SUM function. SQL requires an aggregation function when using GROUP BY.
- C. Incorrect.
This query is incorrect because it adds 'quantity' and 'price' instead of multiplying them, which does not represent revenue calculation.
- D. Incorrect.
This query is incorrect because it uses the AVG function instead of SUM, which calculates the average revenue instead of the total revenue.