APulseBoard 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.
user_logins table.feature_usage and determine whether the user had any usage event from first_login_date + 1 day through first_login_date + 30 days.signup_date from the users table with:
signup_datecohort_sizeretained_usersretention_rate rounded to 4 decimal placessignup_date ascending.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 |
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 |
| signup_date | cohort_size | retained_users | retention_rate |
|---|---|---|---|
| 2024-01-01 | 2 | 1 | 0.5000 |
| 2024-01-02 | 2 | 1 | 0.5000 |
| 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 |
| 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 |
| 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 |
{"users":[[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"],[5,"Ella Singh","2024-01-03","Pro"],[6,"Felix Brown","2024-01-03",null],[7,"Grace Park","2024-01-04","Free"],[8,"Hugo Diaz","2024-01-04","Team"],[9,"Ivy Lopez","2024-01-05","Pro"],[10,"Jack Reed","2024-01-05","Free"],["1","Theo Martin","2024-01-03","null"],["2","Olivia Brooks","2024-01-06","Annual"],["3","Ben Ortiz","2024-01-01","Premium"],["4","Output[["2024-01-01","3","1","0.3333"],["2024-01-02","2","1","0.5000"],["2024-01-03","2","1","0.5000"],["2024-01-04","2","1","0.5000"],["2024-01-05","2","1","0.5000"],["2024-01-08","1","0","0.0000"]]