Calculate 30-Day User Retention | Dataford Interview Questions

Your question is Calculate 30-Day User Retention. Start with the requirements and the three 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

PulseBoard wants to measure whether newly active users come back within 30 days of their first login. Write a SQL query to calculate 30-day retention by signup cohort date.

A user is considered retained if they have at least one feature usage event between 1 and 30 days after their first login date. Ignore usage on the same day as first login.

Requirements

Find each user's first login date from the user_logins table.
Join to feature_usage and determine whether the user had any usage event from first_login_date + 1 day through first_login_date + 30 days.
Return one row per signup_date from the users table with:
- signup_date
- cohort_size
- retained_users
- retention_rate rounded to 4 decimal places
Include cohorts even if retention is 0, and order results by signup_date ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User full name
signup_date	DATE	Date the user signed up
plan_type	VARCHAR(20)	Subscription plan at signup

user_logins

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	User who logged in
login_date	DATE	Date of the login event
device_type	VARCHAR(20)	Device used for login

feature_usage

Column	Type	Description
usage_idPK	INT	Unique feature usage event identifier
user_id	INT	User who used a feature
usage_date	DATE	Date of feature usage
feature_name	VARCHAR(50)	Name of the feature used