Daily Retention Rate Query | Dataford Interview Questions

Your question is Daily Retention Rate Query. 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

Paytm wants to measure how consistently users who signed up in the previous quarter return to the Paytm app. For this exercise, assume the reporting date is 2024-07-01, so the last quarter is 2024-04-01 through 2024-06-30.

Write a PostgreSQL query that calculates daily retention for days 0 through 7 after signup.

Requirements

Include users whose signup_date falls within the last quarter.
Treat a user as retained on day N when they have at least one activity on signup_date + N days.
Use the full signup cohort as the denominator for every retention day, including users with no activity.
Count each user at most once per retention day and return zero-retention days.
Return retention_day, cohort_users, retained_users, and retention_rate, ordered by retention_day.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Paytm user identifier
signup_date	DATE	Date when the user registered
acquisition_channel	VARCHAR(30)	Channel associated with signup

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_at	TIMESTAMP	Timestamp when the activity occurred
activity_type	VARCHAR(30)	Type of Paytm activity