DEA-C01 Question 122
Single answerYou are a data engineer at a company that uses Amazon Redshift for its data warehouse. The marketing team has requested a report on the top 5 products with the highest revenue in the last quarter. The revenue for each product is stored in a 'sales' table that includes columns: 'product_id', 'sale_date', 'quantity', and 'price_per_unit'. Which SQL query should you use to retrieve the required data?
- A
SELECT product_id, SUM(quantity * price_per_unit) AS total_revenue FROM sales WHERE sale_date >= '2023-07-01' AND sale_date <= '2023-09-30' GROUP BY product_id ORDER BY total_revenue DESC LIMIT 5;
- B
SELECT product_id, SUM(quantity + price_per_unit) AS total_revenue FROM sales WHERE sale_date BETWEEN '2023-07-01' AND '2023-09-30' GROUP BY product_id ORDER BY total_revenue DESC LIMIT 5;
- C
SELECT product_id, COUNT(quantity * price_per_unit) AS total_revenue FROM sales WHERE sale_date >= '2023-07-01' AND sale_date <= '2023-09-30' GROUP BY product_id ORDER BY total_revenue DESC LIMIT 5;
- D
SELECT product_id, SUM(quantity * price_per_unit) AS total_revenue FROM sales WHERE sale_date >= '2023-07-01' AND sale_date <= '2023-09-30' GROUP BY product_id HAVING total_revenue > 5000 ORDER BY total_revenue DESC LIMIT 5;
Show answer and explanation
Correct answer: A
Explanation
To retrieve the top 5 products by revenue, you need to calculate total revenue by multiplying 'quantity' and 'price_per_unit', filter the data for the last quarter, group by 'product_id', and order the results in descending order of revenue. The correct query achieves these steps in the right sequence and syntax.
- A. Correct.
This is the correct query. It calculates total revenue for each product by multiplying quantity and price_per_unit, filters the data for the last quarter, groups by product_id, and orders the results in descending order of revenue before limiting the output to the top 5.
- B. Incorrect.
This query incorrectly uses SUM(quantity + price_per_unit), which adds the quantity and price_per_unit instead of calculating the revenue (quantity * price_per_unit). This results in incorrect revenue calculations.
- C. Incorrect.
This query incorrectly uses COUNT(quantity * price_per_unit), which counts the number of occurrences instead of calculating the total revenue.
- D. Incorrect.
This query adds an unnecessary HAVING clause to filter products with total revenue > 5000. While this might work in some cases, it does not meet the requirement to simply retrieve the top 5 products based on revenue.