DEA-C01 Question 348
Select 3You are working as a data engineer for a retail company that processes daily sales data in Amazon Redshift. Your team needs to calculate a 7-day rolling average of sales per product category and generate a pivot table that shows product categories as columns and dates as rows. Which combination of SQL operations should you use in Amazon Redshift to efficiently accomplish this task?
- A
Use the WINDOW function to calculate the 7-day rolling average.
- B
Use the GROUP BY clause to group data by product category and date.
- C
Use the PIVOT function to transform rows into columns for product categories.
- D
Use the COPY command to load data from S3 and then perform aggregation.
- E
Use the UNLOAD command to export the pivoted data to S3 for further analysis.
Show answer and explanation
Correct answers: A, B, C
Explanation
To calculate a 7-day rolling average and create a pivot table in Amazon Redshift, you need to use the WINDOW function to compute the rolling average, GROUP BY to organize data for aggregation, and the PIVOT function to transform rows into columns. COPY and UNLOAD commands are not relevant to the in-database operations required for this task.
- A. Correct.
Correct. The WINDOW function is specifically designed to calculate rolling averages, making it ideal for this use case.
- B. Correct.
Correct. The GROUP BY clause is essential to group sales data by product category and date before applying any aggregation or transformation.
- C. Correct.
Correct. The PIVOT function is necessary to create a pivot table where product categories are columns, and dates are rows.
- D. Incorrect.
Incorrect. While the COPY command helps load data from S3 into Redshift, it is unrelated to the specific operations needed for aggregation, rolling averages, grouping, or pivoting.
- E. Incorrect.
Incorrect. The UNLOAD command is useful for exporting data but is not involved in calculating the rolling average or pivoting data within Redshift.