DEA-C01 Question 331
Single answerYou are working as a data engineer for a retail company. The company stores sales data in an Amazon Redshift table named 'sales' and product information in another table named 'products'. You need to create a query to find the total revenue (sum of 'sales.amount') generated for each product category ('products.category') in the last 30 days. How should you write the query?
- A
SELECT products.category, SUM(sales.amount) FROM sales INNER JOIN products ON sales.product_id = products.product_id WHERE sales.sale_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY products.category;
- B
SELECT SUM(sales.amount) FROM sales LEFT JOIN products ON sales.product_id = products.product_id WHERE sales.sale_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY products.category;
- C
SELECT products.category, SUM(sales.amount) FROM products JOIN sales ON products.product_id = sales.product_id AND sales.sale_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY products.category;
- D
SELECT products.category, SUM(sales.amount) FROM sales JOIN products ON sales.product_id = products.product_id WHERE sales.sale_date >= CURRENT_DATE - 30 GROUP BY products.category;
Show answer and explanation
Correct answer: A
Explanation
To calculate the total revenue by product category for the last 30 days, we need to join the 'sales' and 'products' tables on the 'product_id' column using an INNER JOIN, filter the sales data for the past 30 days using the WHERE clause, and group the results by 'products.category'. The correct syntax for date subtraction in Amazon Redshift is 'CURRENT_DATE - INTERVAL '30 days''.
- A. Correct.
This is the correct query as it correctly performs an INNER JOIN on the tables, filters data for the last 30 days using 'CURRENT_DATE - INTERVAL '30 days'', and groups results by 'products.category'.
- B. Incorrect.
This query performs a LEFT JOIN, which is unnecessary in this scenario as we are only interested in matching records. Additionally, it does not include 'products.category' in the SELECT clause, causing a syntax error.
- C. Incorrect.
This query places the date filter in the ON clause of the JOIN, which is incorrect because filtering should be done in the WHERE clause for proper query semantics.
- D. Incorrect.
This query uses 'CURRENT_DATE - 30' instead of 'CURRENT_DATE - INTERVAL '30 days'', which is not valid in Amazon Redshift SQL syntax.