Welcome to the SQL screen.
The question is on your right: Weekly Service KPI by Team. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You are asked to write a PostgreSQL query to analyze service delivery performance by team from Deloitte operational reporting data. Return one row per team per week for completed service requests, showing total requests, on-time completions, average resolution hours, and a KPI status that labels each team-week as On Track, At Risk, or Needs Attention based on on-time rate and average resolution time.
Use the request creation week for grouping, include only requests created in January 2024, and exclude cancelled requests from the KPI calculations. The result should be sorted by week and team name.
| Column | Type | Description |
|---|---|---|
| team_idPK | INT | Primary key for the service delivery team |
| team_name | VARCHAR(100) | Name of the team |
| region | VARCHAR(50) | Region supported by the team |
| Column | Type | Description |
|---|---|---|
| agent_idPK | INT | Primary key for the service agent |
| team_id | INT | Team assignment for the agent |
| agent_name | VARCHAR(100) | Agent full name |
| active_flag | BOOLEAN | Whether the agent is currently active |
| Column | Type | Description |
|---|---|---|
| request_idPK | INT | Primary key for the service request |
| agent_id | INT | Assigned agent for the request |
| created_at | TIMESTAMP | Timestamp when the request was created |
| completed_at | TIMESTAMP | Timestamp when the request was completed |
| due_at | TIMESTAMP | SLA due timestamp |
| status | VARCHAR(30) | Current request status |
| priority | VARCHAR(20) | Priority level of the request |