Your question is CTE-Based TurboTax Growth Analysis. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given product usage and subscription data for TurboTax. Write a PostgreSQL query that uses a CTE to simplify a growth analysis for the 2024 calendar year. Return one row per month showing the number of users who started a paid subscription in that month, how many of those users were already active in the previous month, and the resulting month-over-month retained signup rate. Only include months with at least 2 paid subscription starts.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique TurboTax user ID |
| signup_date | DATE | Date the user created an account |
| country_code | VARCHAR(2) | User country code |
| acquisition_channel | VARCHAR(30) | Marketing acquisition source |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity event ID |
| user_id | INT | TurboTax user ID |
| activity_date | DATE | Date of product activity |
| surface_name | VARCHAR(30) | TurboTax product surface where activity happened |
| Column | Type | Description |
|---|---|---|
| subscription_idPK | INT | Unique subscription record |
| user_id | INT | TurboTax user ID |
| plan_name | VARCHAR(30) | Subscription plan name |
| subscription_start_date | DATE | Date the paid subscription started |
| subscription_status | VARCHAR(20) | Current subscription status |