DEA-C01 Question 125
Single answerYou are working as a Data Engineer at an e-commerce company. You are tasked with querying data from an Amazon Redshift table named sales_data to calculate the total revenue generated for each product category in a given year. The table has the following schema: order_id, product_category, order_date, revenue. Which SQL query should you use to achieve this?
- A
SELECT product_category, SUM(revenue) AS total_revenue FROM sales_data WHERE EXTRACT(year FROM order_date) = 2023 GROUP BY product_category;
- B
SELECT product_category, SUM(revenue) AS total_revenue FROM sales_data GROUP BY product_category WHERE EXTRACT(year FROM order_date) = 2023;
- C
SELECT product_category, SUM(revenue) AS total_revenue FROM sales_data WHERE TO_CHAR(order_date, 'YYYY') = 2023 GROUP BY product_category;
- D
SELECT product_category, SUM(revenue) AS total_revenue FROM sales_data GROUP BY product_category HAVING EXTRACT(year FROM order_date) = 2023;
Show answer and explanation
Correct answer: A
Explanation
The correct query uses the EXTRACT function to filter rows for the specified year (2023) in the WHERE clause and groups the data by product_category using the GROUP BY clause. The SUM function is then used to calculate the total revenue for each group. Other options fail due to incorrect use of SQL syntax or functions.
- A. Correct.
This is the correct query. The EXTRACT function is used to filter rows for the specified year (2023), and a GROUP BY clause is used to group data by product category. The SUM function calculates the total revenue for each product category.
- B. Incorrect.
This query is invalid because the WHERE clause is incorrectly placed after the GROUP BY clause. In SQL, the WHERE clause must come before the GROUP BY clause.
- C. Incorrect.
This query is incorrect because the TO_CHAR function is not the appropriate way to extract the year from a date column in Amazon Redshift. The EXTRACT function should be used instead.
- D. Incorrect.
This query is incorrect because the HAVING clause is used to filter aggregated data, but here the condition to filter rows by year should be in the WHERE clause.