Weekly Signup Cohort Retention | Dataford Interview Questions

Your question is Weekly Signup Cohort Retention. Start with the requirements and the two tables on the right.

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

Problem

StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.

Requirements

Treat each user's cohort week as the week of signup_date, using DATE_TRUNC('week', signup_date).
For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
Return the number of retained users and the retention rate for each cohort week and week number.
Only include users whose signup_date is within the last 90 days relative to DATE '2024-06-30'.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User who performed the activity
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of user activity