SQL Weekly Cohort Retention | Dataford Interview Questions

PayPal’s Product Growth team wants to measure how consistently newly registered users return after signup. Write a PostgreSQL query that calculates weekly retention for PayPal users who signed up during January 2025.

Requirements

Define each user’s cohort as the calendar week beginning on Monday that contains signup_date.
Report retention for cohort weeks 0 through 4, where week 0 is the signup week.
Count distinct active users in each cohort week. Any non-null activity record qualifies as activity, but activity before signup must be excluded.
Return cohort size, retained users, and retention percentage, ordered by cohort week and retention week.

Schema

users

Column	Type	Description
user_idPK	INT	Unique PayPal user identifier
email	VARCHAR(255)	User email address
signup_date	DATE	Date the user registered

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Date of user activity
activity_type	VARCHAR(50)	Type of PayPal activity

Your question is SQL Weekly Cohort Retention. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem