DEA-C01 practice question: SQL analysis and visualization (1 of 2)
Data Operations and Support A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
A logistics broker tracks the number of parcels handled each day at every depot in an Amazon Redshift table, with one row per depot per day. Days with no parcels are stored with a count of zero.
The operations team wants a dashboard column that shows each depot's average daily volume over the current day and the six days before it, so that short spikes are smoothed out.
Which SQL expression produces the rolling average?
Choose one answer.
AA. AVG(parcels) OVER (PARTITION BY depot_id ORDER BY parcel_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), averaging every day since the first record.
BB. AVG(parcels) OVER (PARTITION BY depot_id, parcel_date), which averages only the rows that share the same depot and day.
CC. SELECT depot_id, AVG(parcels) FROM depot_volume GROUP BY depot_id, which returns one average per depot for the whole table.
DD. AVG(parcels) OVER (PARTITION BY depot_id ORDER BY parcel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Show the answer and explanation
Answer: D
A. Wrong: This window averages every day from the start of the data up to the current row, which is a running average. It keeps growing over time and does not smooth the last seven days.
B. Wrong: This averages only the rows on the same day for each depot, so the value does not roll across days. The average would be the same as the daily value.
C. Wrong: A GROUP BY returns one average per depot for the whole table, not a value per day. The result has no rolling column for the dashboard.
D. Right: Correct. The window partitions rows by depot, orders them by date, and averages the current row with the six rows before it. Each depot's rolling average is calculated separately, which gives the seven-day view the team wants. Learn more: Window functions (opens in a new tab)
Explanation
A rolling average is a window function with a frame of the current row and the six rows before it, ordered by date within each depot. Because every depot has one row per day, six preceding rows cover the six previous days. Running averages, same-day averages and plain group averages do not produce the seven-day view.
Want more on SQL analysis and visualization? The 5 full-length practice sets have 11 more SQL analysis and visualization questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →