Rolling 30-Day Call Center Average | Dataford Interview Questions

Write a SQL query to find the rolling 30-day average of call center volumes partitioned by region.

Use the call_center_daily_volumes table. Treat each window as inclusive of the current date and the preceding 29 calendar days. Exclude NULL volumes from the average, and do not treat missing dates as zero.

Output

One row per region and recorded volume date.
Columns: region, volume_date, daily_volume, and rolling_30_day_avg.
Sort by region, then volume_date ascending.

Schema

call_center_daily_volumes

Column	Type	Description
volume_idPK	INT	Unique call volume record identifier
region	VARCHAR(50)	Call center operating region
volume_date	DATE	Date on which the calls were recorded
call_volume	INT	Number of calls recorded for the region and date

Your question is Rolling 30-Day Call Center Average. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write a SQL query to find the rolling 30-day average of call center volumes partitioned by region.

Output

One row per region and recorded volume date.
Columns: region, volume_date, daily_volume, and rolling_30_day_avg.
Sort by region, then volume_date ascending.

Schema

call_center_daily_volumes

Column	Type	Description
volume_idPK	INT	Unique call volume record identifier
region	VARCHAR(50)	Call center operating region
volume_date	DATE	Date on which the calls were recorded
call_volume	INT	Number of calls recorded for the region and date

Your question is Rolling 30-Day Call Center Average. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.