Calculate First-Week Retention by Signup Cohort

Compute First-Week Retention by Signup Week

You have these tables:

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.