Calculate First-Week Retention by Signup Cohort

## Compute First-Week Retention by Signup Week You have these tables: | Table | Column | Meaning | |---|---|---| | `users` | `user_id` | User identifier. | | `users` | `signup_date` | The user's signup date or timestamp. | | `users` | `plan_type` | The user's plan type. | | `events` | `user_id` | User associated with an event. | | `events` | `event_ts` | Event timestamp. | Group users into cohorts by the calendar week containing `signup_date`. In PostgreSQL, a calendar week begins on Monday at midnight. Name that week-start value `signup_week`. For this exercise, a user is retained if the user has at least one matching event in the half-open interval from `signup_date` through, but not including, `signup_date` plus seven days. An event exactly at signup qualifies; an event exactly seven days later does not. Match events to users by `user_id`. Write one read-only PostgreSQL query that returns one row for each signup-week cohort present in `users`, with these columns in this order: - `signup_week`: the cohort's week-start timestamp. - `cohort_size`: the number of distinct user identifiers in that cohort. - `retained_users`: the number of distinct users in that cohort meeting the first-week event condition. - `retention_rate`: `retained_users` divided by `cohort_size`, rounded to four decimal places. The denominator includes cohort users with no qualifying event. Multiple qualifying events must not count a retained user more than once. Include cohorts with zero retained users; do not add calendar weeks that have no users. Order the result by `signup_week` ascending. If a NULL week-start group is present, place it last.