Write SQL for 7-day geo-localized revenue dashboard

Write a single SQL query (assume PostgreSQL; tz_offset is an integer hour offset from UTC) to compute a 7-day dashboard by local user date for US vs Asia. Treat “today” as 2025-09-01, so the window is 2025-08-26 through 2025-09-01 inclusive, based on each user’s local day defined by event_time_utc + (tz_offset hours). Output columns: local_date, region_group (US or Asia), dau, buyers, revenue_usd, arppu, view_to_purchase_cr, new_user_dau, returning_dau, and a flag is_drop_gt_20pct indicating whether that day’s revenue_usd is >20% below the 7-day median revenue_usd for that region_group. Definitions: dau = distinct users with any event that local day; buyers = distinct users with at least one purchase that day; revenue_usd = sum(revenue_cents)/100 for purchases; arppu = revenue_usd / NULLIF(buyers,0); view_to_purchase_cr = buyers / NULLIF(distinct viewers with at least one view that day,0); new_user_dau = distinct users whose signup_at local date is within the 7-day window and who had any event that day; returning_dau = dau − new_user_dau. Provide the exact query, handling time zone conversion, inclusive window bounds, and ensuring idempotence if events are re-sent with identical (user_id, event_time_utc, event_type, session_id). Then briefly state one indexing strategy to make it fast. Schema and sample data: users(user_id INT PK, region TEXT, tz_offset INT, signup_at TIMESTAMP UTC) +---------+-------------+-----------+---------------------+ | user_id | region | tz_offset | signup_at | +---------+-------------+-----------+---------------------+ | 1 | US/Pacific | -8 | 2025-08-20 10:00:00 | | 2 | US/Eastern | -5 | 2025-08-27 02:00:00 | | 3 | Asia/Shanghai| 8 | 2025-08-25 14:00:00 | | 4 | Asia/Tokyo | 9 | 2025-08-28 23:30:00 | | 5 | US/Pacific | -8 | 2025-08-30 16:45:00 | +---------+-------------+-----------+---------------------+ events(user_id INT, event_time_utc TIMESTAMP UTC, event_type TEXT, revenue_cents INT, session_id TEXT) +---------+---------------------+------------+---------------+------------+ | user_id | event_time_utc | event_type | revenue_cents | session_id | +---------+---------------------+------------+---------------+------------+ | 1 | 2025-08-26 07:10:00 | view | NULL | s1 | | 1 | 2025-08-26 07:12:00 | purchase | 499 | s1 | | 2 | 2025-08-27 06:00:00 | view | NULL | s2 | | 3 | 2025-08-31 18:20:00 | view | NULL | s3 | | 3 | 2025-09-01 02:05:00 | purchase | 299 | s3 | | 4 | 2025-08-28 15:55:00 | view | NULL | s4 | | 4 | 2025-08-28 16:05:00 | purchase | 199 | s4 | | 5 | 2025-08-30 23:50:00 | view | NULL | s5 | | 5 | 2025-08-31 00:10:00 | view | NULL | s6 | | 5 | 2025-09-01 08:00:00 | purchase | 999 | s6 | +---------+---------------------+------------+---------------+------------+