Rolling Retention in SQL

Cisco Webex analytics needs a daily engagement report for a selected reporting period. Write a PostgreSQL query that aggregates daily active users and calculates rolling seven-day retention metrics.

Requirements

1. Count distinct users with a session event on each reporting date as daily_active_users.
2. Count distinct users with a session event during the current date and six preceding dates as rolling_7d_active_users.
3. Calculate rolling_7d_retention_pct as rolling seven-day active users divided by users enrolled on or before the reporting date.
4. Return one row per reporting date, including dates with no qualifying activity, ordered chronologically. Exclude users with a NULL enrollment date and events that are not session events.