Retention Rate for a Series

Prime Video wants to measure whether viewers return to watch the same series after their initial viewing. Write a PostgreSQL query to calculate the 30-day retention rate for users who first watched series 101, The Peripheral, during January 2025.

Requirements

1. Define the cohort as distinct users whose first qualifying view of series 101 occurred from 2025-01-01 through 2025-01-31.
2. Count a user as retained if they watched series 101 again more than 0 and no more than 30 days after their first qualifying view.
3. Return the cohort size, retained-user count, and retention rate as a percentage rounded to two decimal places.
4. Prevent multiple follow-up views from counting a user more than once.

Output details

• Return one row with columns cohort_users, retained_users, and retention_rate_pct, in that order.
• Return NULL for retention_rate_pct when the cohort contains zero users.