How would you approach optimizing a slow-running SQL query in a distributed environment?
Use PostgreSQL SQL to produce an optimized version that limits scanned data, avoids unnecessary row multiplication, and preserves the required results. The query should summarize qualifying user activity by region and month.
Output
- One row per region and activity month, with
region, month_start, active_users, total_events, and top_3_event_total.
- Include only active users and
view or click events from January and February 2025.
- Sort by
region, then month_start, both ascending.
Output details
- Treat users as active only when
user_accounts.status = 'active'.
- Include events with timestamps from
2025-01-01 00:00:00 inclusive through 2025-03-01 00:00:00 exclusive.
- Define the top three as users ranked by monthly event count descending, breaking ties by
user_id ascending.
- Sum event counts for those three ranked users as
top_3_event_total.
