Rolling Desk Utilization With SQL | Dataford Interview Questions

Write a query using SQL window functions to calculate the rolling three-month average of desk utilization.

Calculate utilization as desks used divided by desks available, expressed as a percentage. Treat a zero capacity as undefined and allow missing utilization values to be ignored by the average.

Output

One row per matched location and month.
Columns: location_name, month_start, utilization_rate, and rolling_three_month_avg.
Include the current month and up to the prior two calendar months for each location.
Order by location_name, then month_start.

Schema

locations

Column	Type	Description
location_idPK	INT	Unique identifier for a coworking location
location_name	VARCHAR(100)	Name of the coworking location

desk_utilization

Column	Type	Description
utilization_idPK	INT	Unique monthly utilization record identifier
location_id	INT	Location associated with the utilization record
month_start	DATE	First day of the utilization month
desks_available	INT	Number of desks available during the month
desks_used	INT	Number of desks used during the month

Your question is Rolling Desk Utilization With SQL. Start with the requirements and the two tables 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