Track Agency Utilization Query | Dataford Interview Questions

Your question is Track Agency Utilization Query. Start with the requirements and the four tables on the right.

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

Problem

Omnicom Health operations leaders need a weekly view of team utilization across agency staff. Write a PostgreSQL query using the capacity and time-entry data below.

Requirements

Return one row per team and capacity week from January 6 through January 19, 2025.
Calculate total logged hours and total available hours for each team and week. Include weeks with capacity but no time entries.
Calculate utilization_rate as logged hours divided by available hours, expressed as a percentage rounded to two decimals. Return 0.00 when the available-hours total is zero.
Order results by team name and week start.

Schema

teams

Column	Type	Description
team_idPK	INT	Primary key for an agency team
team_name	VARCHAR(100)	Agency team name

employees

Column	Type	Description
employee_idPK	INT	Primary key for an employee
employee_name	VARCHAR(100)	Employee's name
team_id	INT	Team assigned to the employee
role	VARCHAR(80)	Employee's agency role

weekly_capacity

Column	Type	Description
employee_idPK	INT	Employee with planned weekly capacity
week_startPK	DATE	Monday starting the capacity week
available_hours	NUMERIC(6,2)	Hours available to be scheduled

time_entries

Column	Type	Description
entry_idPK	INT	Primary key for a time entry
employee_id	INT	Employee who logged the hours
entry_date	DATE	Date the work was logged
hours	NUMERIC(6,2)	Hours logged, possibly missing