Business Context
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.
Task
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.
Requirements
- Filter deleted messages, non-standard messages, inactive workspaces, and inactive authors before aggregation.
- Pre-aggregate reactions by message to avoid multiplying message rows during joins.
- Calculate message count, distinct active authors, total reactions, and reactions per message.
- Use
LAG to show the previous observed week's message count and percentage change.
- Return only weeks with at least two messages, ordered by workspace and week.
