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.
Output details
- Return columns in this order:
team_name, week_start, message_count, active_author_count, reaction_count, reactions_per_message, previous_week_messages, week_over_week_pct.
- Include timestamps from
2025-01-01 inclusively through, but excluding, 2025-03-01.
- Use Monday as each week's
week_start, allowing the first displayed week to begin before 2025-01-01.
- Order results by
team_name ascending, then week_start ascending.
- Round
reactions_per_message and week_over_week_pct to two decimal places.
- Report zero reactions as
0, and report unavailable or zero-denominator previous-week comparisons as NULL.
