SQL Bottlenecks by Team and Week | Dataford Interview Questions

Your question is SQL Bottlenecks by Team and Week. Start with the requirements and the two 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

Private Practice wants to identify operational bottlenecks that take the longest to resolve. Write a PostgreSQL query that summarizes resolved cases by team, calendar week, and bottleneck type, then returns the five groups with the highest average resolution time.

Requirements

Join operational_cases to teams and group results by team, week, and bottleneck type.
Include only resolved cases with non-null timestamps and bottleneck types.
Calculate average resolution time in hours and the number of resolved cases in each group.
Return the five highest averages, using a deterministic tie-breaker after sorting by average resolution time descending.

Representative Data

Schema

teams

Column	Type	Description
team_idPK	INT	Unique operations team identifier
team_name	VARCHAR(100)	Private Practice operations team name

operational_cases

Column	Type	Description
case_idPK	INT	Unique operational case identifier
team_id	INT	Assigned operations team identifier
bottleneck_type	VARCHAR(100)	Category describing the operational bottleneck
created_at	TIMESTAMP	Timestamp when the case was created
resolved_at	TIMESTAMP	Timestamp when the case was resolved
status	VARCHAR(20)	Current case status