Running Average With Window Functions

Hinge's analytics team wants to monitor engagement with the Discover experience. Write a PostgreSQL query that calculates each user's rolling average of daily interactions over the preceding 30 calendar days, including the current interaction date.

Use 2026-03-01 as the report date. Only interactions from 2026-02-01 through 2026-03-01, inclusive, should be considered.

Requirements

1. Aggregate multiple interactions for the same user on the same date into daily_interactions.
2. Calculate a 30-day rolling average per user with a window function.
3. Include only users with at least one interaction in the reporting period.
4. Return results ordered by user_id and interaction_date.

Output details

• Return columns in this order: user_id, display_name, interaction_date, daily_interactions, rolling_avg_30d.
• Order rows by user_id ascending, then interaction_date ascending.
• Round rolling_avg_30d to 4 decimal places.