D8LooPFocus modeCODE WORKSPACE
Pacific daily active users
Return pacific_event_day and active_users (distinct users) bucketed into a Pacific-style reporting day. Order by pacific_event_day.
Bucket with date(datetime(event_time, '-8 hours')), then COUNT(DISTINCT user_id).
Sample input
eventsevent_time is UTC; shift -8h before truncating. Events at 03:30 and 04:10 UTC fall on the previous Pacific day.
| event_id | user_id | event_time |
|---|---|---|
| 1 | 1 | 2026-01-01 03:30:00 |
| 2 | 1 | 2026-01-01 04:10:00 |
| 3 | 2 | 2026-01-01 22:15:00 |
| 4 | 2 | 2026-01-02 00:30:00 |
| 5 | 3 | 2026-01-02 05:00:00 |
| 6 | 1 | 2026-01-03 18:00:00 |
Expected output
Expected outputDistinct users per Pacific-style day. 2026-01-01 has users 2 and 3 (events 3, 4, 5).
| pacific_event_day | active_users |
|---|---|
| 2025-12-31 | 1 |
| 2026-01-01 | 2 |
| 2026-01-03 | 1 |
Constraints
Shift event_time by a fixed -8 hours before truncating: date(datetime(event_time, '-8 hours')). GROUP BY that day with COUNT(DISTINCT user_id). Order by pacific_event_day.
Expected skills
Timezone shifting before truncation and distinct active-user counts.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.