Acumen wants to identify its most engaged users in each region. Write a PostgreSQL query that calculates engagement scores from recent Acumen activity and returns the top three ranks per region, preserving ties.
Use 2026-08-29 12:00:00+00 as the report timestamp so the result is reproducible. The reporting window includes events from the preceding 30 days, including the timestamp boundary.
Requirements
- Join users to engagement events and event types to calculate each user's score.
- Assign points from
event_types.points and treat users with no scored activity as having a score of zero.
- Rank users independently within each region using a tie-aware window function.
- Return every user whose rank is at most 3, including all users tied at the third rank.
- Order results by region, rank, and user ID.
Representative data
| user_id | user_name | region |
|---|
| 1 | Ava Chen | North |
| 2 | Ben Ortiz | North |
| 3 | Cara Singh | North |
| 5 | Emi Park | South |
| 9 | Iris Cole | West |
| 12 | Liam Reed | East |
| event_id | user_id | event_type_id | occurred_at |
|---|
| 101 | 1 | 3 | 2026-08-28 09:00:00+00 |
| 103 | 2 | 5 | 2026-08-01 14:00:00+00 |
| 104 | 3 | 4 | 2026-08-02 10:00:00+00 |
| 107 | 5 | 5 | 2026-08-15 08:00:00+00 |
| 112 | 9 | 5 | 2026-08-27 16:00:00+00 |
| 115 | 4 | 5 | 2026-07-01 12:00:00+00 |
