Your question is Optimize SQL for Large Datasets. Start with the requirements and the four 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.
Microsoft Teams analytics needs a weekly engagement report across active workspaces. The source tables contain millions of messages and reactions, so the query must reduce data before expensive joins and window calculations.
Write an optimized PostgreSQL query for messages sent from 2025-01-01 through 2025-02-28. Return weekly metrics for active Teams workspaces and retain weeks with at least two qualifying messages.
LAG to show the previous observed week's message count and percentage change.| Column | Type | Description |
|---|---|---|
| team_idPK | INT | Microsoft Teams workspace identifier |
| team_name | VARCHAR(100) | Workspace name |
| status | VARCHAR(20) | Workspace lifecycle status |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Microsoft Entra user identifier |
| display_name | VARCHAR(100) | User display name |
| account_status | VARCHAR(20) | User account status |
| Column | Type | Description |
|---|---|---|
| message_idPK | BIGINT | Channel message identifier |
| team_id | INT | References teams.team_id |
| author_id | INT | References users.user_id |
| sent_at | TIMESTAMP | Message creation timestamp |
| is_deleted | BOOLEAN | Whether the message was deleted |
| message_type | VARCHAR(30) | Message classification |
| Column | Type | Description |
|---|---|---|
| reaction_idPK | BIGINT | Reaction identifier |
| message_id | BIGINT | References channel_messages.message_id |
| user_id | INT | User who added the reaction |
| reaction_type | VARCHAR(30) | Reaction category |