Given a dataset with a timestamp column and user events, write a SQL query to compute a 7-day rolling average of events per user using window functions.
Use the provided user, event, and calendar-date tables. Treat dates without events as zero-event days.
Output
- One row per user and calendar date.
- Columns:
user_id, event_date, daily_event_count, and rolling_7_day_avg.
- Include all users and calendar dates, sort by
user_id, then event_date.
Output details
- Sort
user_id and event_date in ascending order.
- Round
rolling_7_day_avg to two decimal places.
- Include the current date and the preceding six calendar dates in the rolling window, with both bounds inclusive.
