Write a SQL query to combine project, resource, and performance tables and calculate weekly operational KPIs by team at CACI.
Use the supplied tables and calculate results for weeks represented in the performance data. Include week-over-week utilization change when a prior week exists.
team, week_start, project_count, resource_count, utilization_pct, avg_performance_score, on_time_pct, and utilization_change_pct.team, then week_start ascending.| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Unique project identifier |
| project_name | VARCHAR(100) | Project name |
| team | VARCHAR(50) | CACI operational team responsible for the project |
| status | VARCHAR(20) | Current project status |
| Column | Type | Description |
|---|---|---|
| resource_idPK | INT | Unique resource identifier |
| project_id | INT | Assigned project identifier |
| resource_name | VARCHAR(100) | Resource name |
| role | VARCHAR(60) | Operational role |
| Column | Type | Description |
|---|---|---|
| performance_idPK | INT | Unique performance record identifier |
| resource_id | INT | Measured resource identifier |
| week_start | DATE | Monday starting the reporting week |
| hours_logged | NUMERIC(8,2) | Hours logged during the week |
| target_hours | NUMERIC(8,2) | Expected hours for the week |
| performance_score | NUMERIC(5,2) | Weekly performance score |
| tasks_completed | INT | Tasks completed during the week |
| tasks_on_time | INT | Completed tasks delivered on time |