SQL: Month-over-Month Service Trends | Dataford Interview Questions

Write a SQL query to compare monthly service request volume and completion rate by site, using a window function to show month-over-month change.

Use the service_sites and service_requests tables. Treat a request as completed when completed_at is not NULL. Include every site for every month in the submitted-request date range, including months with no requests.

Output

One row per site and calendar month, with columns site_id, site_name, month, request_volume, completion_rate, volume_mom_change, and completion_rate_mom_change.
Show completion rate as a percentage, and month-over-month changes as current month minus prior month. Use NULL for changes when no prior month exists.
Order by site_id, then month ascending.

Schema

service_sites

Column	Type	Description
site_idPK	INT	Unique identifier for a service site
site_name	VARCHAR(100)	Display name of the service site

service_requests

Column	Type	Description
request_idPK	INT	Unique identifier for a service request
site_id	INT	Site associated with the request
submitted_at	DATE	Date the request was submitted
completed_at	DATE	Date the request was completed

Your question is SQL: Month-over-Month Service Trends. 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