Welcome to the SQL screen.
The question is on your right: Calculate 30-Day User Retention. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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.
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.| 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 |