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 |
+---------+---------------------+------------+---------------+------------+

7-day Geo-localized Revenue Dashboard with Time Zone Conversion (PostgreSQL)

Using PostgreSQL, write a single SQL query to compute a 7-day dashboard by local user date for US vs Asia.

Treat “today” as 2025-06-01, so the reporting window is from 2025-05-26 through 2025-06-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
• is_drop_gt_20pct (BOOLEAN) indicating whether that day’s revenue_usd is more than 20% below the 7-day median revenue_usd for that region_group.

Definitions:

• dau = DISTINCT users with any event on that local_date.
• buyers = DISTINCT users with at least one purchase on that local_date.
• revenue_usd = SUM(revenue_cents) / 100 for purchase events on that local_date.
• arppu = revenue_usd / NULLIF(buyers, 0).
• view_to_purchase_cr = buyers / NULLIF(DISTINCT viewers with at least one view on that local_date, 0).
• new_user_dau = DISTINCT users active that local_date whose signup_at local date is between 2025-05-26 and 2025-06-01 inclusive.
• returning_dau = dau − new_user_dau.

Only include users in the US or Asia, where region_group is defined as:

• 'US' when users.region LIKE 'US/%'
• 'Asia' when users.region LIKE 'Asia/%'.

The query must:

• Correctly handle time zone conversion using tz_offset (integer hour offset from UTC).
• Use the local_date window [2025-05-26, 2025-06-01] inclusively.
• Ensure idempotence if events are re-sent with identical (user_id, event_time_utc, event_type, session_id) by deduplicating those events.
• Compute the 7-day median revenue_usd per region_group over all 7 days (including days with zero revenue) and flag is_drop_gt_20pct when revenue_usd < 0.8 * median_7d for that region_group.

Assume the following schema and sample data.

Table: users

• user_id INT PRIMARY KEY
• region TEXT (e.g., 'US/Pacific', 'Asia/Shanghai')
• tz_offset INT (integer hours offset from UTC)
• signup_at TIMESTAMP (stored in UTC)

Table: events

• user_id INT
• event_time_utc TIMESTAMP (UTC)
• event_type TEXT (e.g., 'view', 'purchase')
• revenue_cents INT (NULL for non-purchase events)
• session_id TEXT

Write the exact SQL query that produces the requested dashboard for the given window, and ensure it would work on the sample data. Then, in a short SQL comment at the end of your query, briefly state one indexing strategy that would make this query fast on large datasets.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
region	VARCHAR(50)	NOT NULL
tz_offset	INT	NOT NULL
signup_at	TIMESTAMP	NOT NULL

Sample data:

region	signup_at	tz_offset	user_id
US/Pacific	2025-05-20 10:00:00	-8	1
US/Eastern	2025-05-27 02:00:00	-5	2
Asia/Shanghai	2025-05-25 14:00:00	8	3
Asia/Tokyo	2025-05-28 23:30:00	9	4
US/Pacific	2025-05-30 16:45:00	-8	5

Table events

column	type	constraints
user_id	INT	NOT NULL
event_time_utc	TIMESTAMP	NOT NULL
event_type	VARCHAR(20)	NOT NULL
revenue_cents	INT
session_id	VARCHAR(50)	NOT NULL

Sample data:

event_time_utc	event_type	revenue_cents	session_id	user_id
2025-05-26 07:10:00	view	NULL	s1	1
2025-05-26 07:12:00	purchase	499	s1	1
2025-05-27 06:00:00	view	NULL	s2	2
2025-05-31 18:20:00	view	NULL	s3	3
2025-06-01 02:05:00	purchase	299	s3	3
2025-05-28 15:55:00	view	NULL	s4	4
2025-05-28 16:05:00	purchase	199	s4	4
2025-05-28 16:05:00	purchase	199	s4	4
2025-05-30 23:50:00	view	NULL	s5	5
2025-05-31 00:10:00	view	NULL	s6	5
2025-06-01 08:00:00	purchase	999	s6	5

Expected output

Expected 7-day local-date dashboard rows by region. User 5's 2025-06-01 08:00:00 UTC purchase with tz_offset -8 falls on local 2025-06-01, not 2025-05-31.

arppu	buyers	dau	is_drop_gt_20pct	local_date	new_user_dau	region_group	returning_dau	revenue_usd	view_to_purchase_cr
NULL	0	0	false	2025-05-26	0	Asia	0	0.0	NULL
NULL	0	0	false	2025-05-26	0	US	0	0.0	NULL
NULL	0	0	false	2025-05-27	0	Asia	0	0.0	NULL
NULL	0	1	false	2025-05-27	1	US	0	0E-20	0E-20
NULL	0	0	false	2025-05-28	0	Asia	0	0.0	NULL
NULL	0	0	false	2025-05-28	0	US	0	0.0	NULL
1.99000000000000000000	1	1	false	2025-05-29	1	Asia	0	1.9900000000000000	1.00000000000000000000
NULL	0	0	false	2025-05-29	0	US	0	0.0	NULL
NULL	0	0	false	2025-05-30	0	Asia	0	0.0	NULL
NULL	0	1	false	2025-05-30	1	US	0	0E-20	0E-20
NULL	0	0	false	2025-05-31	0	Asia	0	0.0	NULL
NULL	0	0	false	2025-05-31	0	US	0	0.0	NULL
2.9900000000000000	1	1	false	2025-06-01	0	Asia	1	2.9900000000000000	1.00000000000000000000
9.9900000000000000	1	1	false	2025-06-01	1	US	0	9.9900000000000000	NULL

Hints

1. Compute the regional median in a separate grouped CTE, then join it to the daily rows.
2. Use a generated date series so days with no activity still appear.