Task
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_datecohort_sizeretained_usersretention_rate rounded to 4 decimal places
- Include cohorts even if retention is 0, and order results by
signup_date ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| user_name | VARCHAR(100) | User name |
| signup_date | DATE | User signup date |
| plan_type | VARCHAR(20) | Subscription plan |
user_logins
| column | type | description |
|---|
| login_id | INT | Unique login event identifier |
| user_id | INT | User who logged in |
| login_date | DATE | Login date |
| device_type | VARCHAR(20) | Device used for login |
feature_usage
| column | type | description |
|---|
| usage_id | INT | Unique feature usage event identifier |
| user_id | INT | User who used a feature |
| usage_date | DATE | Feature usage date |
| feature_name | VARCHAR(50) | Feature used |
Sample Data
users
| user_id | user_name | signup_date | plan_type |
|---|
| 1 | Ava Chen | 2024-01-01 | Free |
| 2 | Ben Ortiz | 2024-01-01 | Pro |
| 3 | Chloe Kim | 2024-01-02 | Free |
| 4 | Daniel Wu | 2024-01-02 | Team |
user_logins
| login_id | user_id | login_date | device_type |
|---|
| 101 | 1 | 2024-01-03 | Web |
| 102 | 1 | 2024-01-10 | iOS |
| 103 | 2 | 2024-01-05 | Android |
| 104 | 3 | 2024-01-04 | Web |
feature_usage
| usage_id | user_id | usage_date | feature_name |
|---|
| 1001 | 1 | 2024-01-20 | Dashboard |
| 1002 | 2 | 2024-02-10 | Export |
| 1003 | 3 | 2024-01-04 | Search |
| 1004 | 3 | 2024-01-25 | Alerts |
Expected Output
| signup_date | cohort_size | retained_users | retention_rate |
|---|
| 2024-01-01 | 2 | 1 | 0.5000 |
| 2024-01-02 | 2 | 1 | 0.5000 |
