Your question is SQL for Weekly Site Performance. 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.
Diligent Robotics operations leaders need a weekly view of how sites using Moxi are performing. The report should show completed task volume and turnaround trends while preserving sites and robots that may have no completed work.
Write a PostgreSQL query that analyzes completed tasks by site and Monday-based calendar week. Use started_at to determine the week and calculate turnaround from started_at to completed_at.
status = 'completed' and non-null timestamps.LAG to calculate week-over-week percentage change for both completed task count and average turnaround, comparing each site with its previous reported week.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique site identifier |
| site_name | VARCHAR(100) | Hospital or facility name |
| city | VARCHAR(80) | Site city |
| active | BOOLEAN | Whether the site is currently active |
| Column | Type | Description |
|---|---|---|
| robot_idPK | INT | Unique Moxi robot identifier |
| site_id | INT | Assigned site identifier |
| robot_name | VARCHAR(80) | Robot display name |
| commissioned_at | DATE | Date the robot entered service |
| Column | Type | Description |
|---|---|---|
| task_idPK | INT | Unique task identifier |
| site_id | INT | Site where the task occurred |
| robot_id | INT | Assigned Moxi robot |
| status | VARCHAR(30) | Task lifecycle status |
| started_at | TIMESTAMPTZ | Task start timestamp |
| completed_at | TIMESTAMPTZ | Task completion timestamp |