SQL for Weekly Performance | Dataford Interview Questions

Clear Operations needs a weekly view of performance across Clear locations. Write a PostgreSQL query that reports completed transaction volume, average customer wait time, and the week-over-week change in completed transactions for each location.

Requirements

Include only transactions whose status is completed.
Group results by location and calendar week, using Monday as the week start.
Calculate completed transaction count and average wait time in minutes.
Use a window function to calculate the percentage change in completed transactions from the previous week for the same location. Return NULL when no prior week exists.
Include only location-week groups with completed transactions and order the output by location name and week.

Schema

locations

Column	Type	Description
location_idPK	INT	Primary key for the Clear location
location_name	VARCHAR(100)	Name of the Clear location

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
location_id	INT	Location associated with the transaction
status	VARCHAR(20)	Transaction processing status
completed_at	TIMESTAMPTZ	Timestamp when the transaction completed
wait_minutes	INT	Customer wait time in minutes

Your question is SQL for Weekly Performance. 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