Write SQL for 7-day geo-localized revenue dashboard
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
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 |
+---------+---------------------+------------+---------------+------------+
Overview: This question evaluates SQL skills in time-zone aware local-date aggregation, idempotent event deduplication, cohorting for new versus returning users, calculation of revenue and conversion metrics, and anomaly detection via median-based drop flags.
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.
Tables
users(user_id INT, region VARCHAR(50), tz_offset INT, signup_at TIMESTAMP)
events(user_id INT, event_time_utc TIMESTAMP, event_type VARCHAR(20), revenue_cents INT, session_id VARCHAR(50))
Hints
- Compute the regional median in a separate grouped CTE, then join it to the daily rows.
- Use a generated date series so days with no activity still appear.