Calculate 30-Day User Retention

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

1. Find each user's first login date from the user_logins table.
2. 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.
3. Return one row per signup_date from the users table with:
   • signup_date
   • cohort_size
   • retained_users
   • retention_rate rounded to 4 decimal places
4. Include cohorts even if retention is 0, and order results by signup_date ascending.

Table Definitions

users

user_logins

feature_usage