Optimize a slow-running SQL query that joins several large tables representing Scale AI user annotation histories. Write a PostgreSQL query that returns approved annotation activity from January 2025, considering only each task's latest history event through January 31. Filter to active projects and aggregate results by user and project.
user_id, username, project_id, project_name, approved_tasks, total_duration_seconds, last_approved_atuser_id, then project_iduser_id and project_id in ascending order.event_at using highest history_id first.2025-01-01 00:00:00 and exclusive 2025-02-01 00:00:00.duration_seconds as 0 when calculating total_duration_seconds.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Scale AI user identifier |
| username | VARCHAR(100) | User display name |
| team | VARCHAR(100) | User's annotation team |
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Scale AI project identifier |
| project_name | VARCHAR(150) | Project name |
| is_active | BOOLEAN | Whether the project is currently active |
| Column | Type | Description |
|---|---|---|
| task_idPK | INT | Annotation task identifier |
| project_id | INT | Referenced project |
| assigned_user_id | INT | User assigned to the task |
| task_type | VARCHAR(80) | Annotation task type |
| Column | Type | Description |
|---|---|---|
| history_idPK | INT | Annotation history event identifier |
| task_id | INT | Referenced annotation task |
| status | VARCHAR(30) | Task status at this event |
| event_at | TIMESTAMP | Time of the status event |
| duration_seconds | INT | Annotation duration in seconds |