Month-Over-Month Active User Retention

Write a query to find the active user retention rate month-over-month, utilizing complex multi-table joins.

Use the provided user, session, subscription, plan, and region data. Treat a user as active in a month when they have a valid session, an active account, and an active subscription plan during that month.

Output

1. One row per calendar month with activity data.
2. Columns: month_start, active_users, previous_month_active_users, retained_users, and retention_rate_pct.
3. Include months from the first through the last session month, including months with no activity. Order by month_start ascending.

Output details

• Count each user at most once per calendar month, even with multiple qualifying sessions.
• Treat a session as valid only when session_status = TRUE.
• Treat a subscription as active at session time when started_at is inclusive and ended_at is NULL or strictly later.
• Calculate retention as retained users divided by the immediately preceding calendar month's active users, multiplied by 100.
• Round retention_rate_pct to two decimal places and return NULL when the denominator is zero or unavailable.
• Return NULL for previous_month_active_users and retention_rate_pct in the first output month.