Calculate 30-Day User Retention

HardSQL · PostgreSQL

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

Tablesusersuser_loginsfeature_usage