Daily Active Users with a 3-Day Average
From app_events, compute daily active users (dau): the number of distinct users with at least one event on that date. Only dates with events appear.
For each such date also compute avg_3d: the average dau over the calendar window from 2 days before through that date, using only the dates in the window that have events. Round with ROUND(x, 2).
Return columns event_date, dau, avg_3d. Order by event_date ascending.
Schema
CREATE TABLE app_events ( event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, event_date TEXT NOT NULL, event_name TEXT NOT NULL );
Sample data
app_events
| event_id | user_id | event_date | event_name |
|---|---|---|---|
| 1 | 1 | 2026-06-01 | open |
| 2 | 2 | 2026-06-01 | open |
| 3 | 1 | 2026-06-01 | click |
| 4 | 1 | 2026-06-02 | open |
| 5 | 3 | 2026-06-02 | open |
| 6 | 4 | 2026-06-02 | open |
| 7 | 2 | 2026-06-03 | open |
| 8 | 1 | 2026-06-04 | open |
| 9 | 2 | 2026-06-04 | open |
| 10 | 3 | 2026-06-04 | open |
| 11 | 4 | 2026-06-04 | open |
Hint 1
A ROWS frame counts rows, not days. When dates are missing, "2 rows back" can be a week back.
Hint 2
Order the window by julianday(event_date) and use RANGE BETWEEN 2 PRECEDING AND CURRENT ROW.
Good practice for: Data Analyst, Product Manager, Data Scientist, Data Engineer
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.