Your question is Monthly Team Performance Cost Analysis. Start with the requirements and the three 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.
You are given operational activity and cost data from a brokerage operations workflow. Write a PostgreSQL query to report monthly performance by team for Gallagher teams, combining activity records with monthly team costs. Your result should show each team and month with total completed volume, average turnaround time in hours, and cost per unit. Teams with activity but no matching monthly cost should still appear, and cost per unit should safely handle missing or zero volume.
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record identifier |
| team_id | INT | Reference to the operations team |
| case_id | VARCHAR(20) | Operational case reference |
| completed_at | TIMESTAMP | Timestamp when the activity was completed |
| turnaround_hours | NUMERIC(8,2) | Turnaround time in hours |
| units_processed | INT | Number of units completed in the activity |
| status | VARCHAR(20) | Activity status |
| Column | Type | Description |
|---|---|---|
| cost_idPK | INT | Unique cost record identifier |
| team_id | INT | Reference to the operations team |
| cost_month | DATE | First day of the month for the cost record |
| monthly_cost | NUMERIC(12,2) | Total monthly operating cost for the team |
| Column | Type | Description |
|---|---|---|
| team_idPK | INT | Unique team identifier |
| team_name | VARCHAR(100) | Operations team name |