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_id| 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 |