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.
tt_users| column | type | description |
|---|---|---|
| user_id | INT | Unique TurboTax user ID |
| signup_date | DATE | Date the user created an account |
| country_code | VARCHAR(2) | User country |
| acquisition_channel | VARCHAR(30) | Marketing acquisition source |
tt_activity| column | type | description |
|---|---|---|
| activity_id | INT | Unique activity event ID |
| user_id | INT | TurboTax user ID |
| activity_date | DATE | Date of product activity |
| surface_name | VARCHAR(30) | TurboTax surface where activity happened |
tt_subscriptions| column | type | description |
|---|---|---|
| subscription_id | INT | Unique subscription record |
| user_id | INT | TurboTax user ID |
| plan_name | VARCHAR(30) | Subscription plan |
| subscription_start_date | DATE | Paid subscription start date |
| subscription_status | VARCHAR(20) | Current status |
tt_users| user_id | signup_date | country_code | acquisition_channel |
|---|---|---|---|
| 3 | 2023-12-20 | US | paid_search |
| 1 | 2024-01-03 | US | organic |
| 8 | 2024-03-12 | US | affiliate |
tt_activity| activity_id | user_id | activity_date | surface_name |
|---|---|---|---|
| 102 | 1 | 2024-01-18 | deductions |
| 101 | 3 | 2024-01-10 | filing_flow |
| 110 | 8 | 2024-04-05 | filing_flow |
tt_subscriptions| subscription_id | user_id | plan_name | subscription_start_date | subscription_status |
|---|---|---|---|---|
| 201 | 3 | Deluxe | 2024-02-02 | active |
| 202 | 1 | Free | 2024-01-20 | cancelled |
| 208 | 8 | Premium | 2024-04-07 | active |
| signup_month | paid_starts | prior_month_active_users | retained_signup_rate |
|---|---|---|---|
| 2024-02 | 2 | 2 | 1.0000 |
| 2024-04 | 3 | 2 | 0.6667 |
| 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 |
| user_id | signup_date | country_code | acquisition_channel |
|---|---|---|---|
| 5 | 2024-02-25 | CA | organic |
| 3 | 2023-12-20 | US | paid_search |
| 1 | 2024-01-03 | US | organic |
| 7 | 2024-03-01 | referral | |
| 2 | 2024-01-15 | US | |
| 9 | 2024-04-01 | US | |
| 4 | 2024-02-10 | UK | affiliate |
| 6 | 2024-02-28 | US | paid_search |
| 8 | 2024-03-12 | US | affiliate |
| 10 | 2024-05-10 | CA | organic |
| 1 | 2024-05-08 | NZ | |
| 2 | 2024-01-04 | null | null |
| 3 | 2024-01-15 | JP | |
| 4 | 2024-03-08 | CA | affiliate |
| 5 | 2024-04-12 | NL | null |
| 6 | 2024-01-30 | AU | affiliate |
| 7 | 2024-04-17 | ZA | social |
| 8 | 2023-12-20 | NZ | newsletter |
| 9 | 2024-04-14 | null | referral |
| 10 | 2024-04-30 | SE | null |
| 11 | 2024-04-04 | DK | direct |
| 12 | 2024-01-13 | MY | affiliate |
| 13 | 2024-04-23 | DK | partner |
| 14 | 2024-05-07 | CH | retargeting |
| 15 | 2024-03-28 | IE | content |
| 16 | 2024-01-22 | MX | podcast |
| 17 | 2023-12-23 | CH |
| activity_id | user_id | activity_date | surface_name |
|---|---|---|---|
| 110 | 8 | 2024-04-05 | filing_flow |
| 101 | 3 | 2024-01-10 | filing_flow |
| 107 | 6 | 2024-03-02 | home |
| 103 | 1 | 2024-01-25 | refund_tracker |
| 112 | 9 | 2024-03-30 | deductions |
| 105 | 4 | 2024-01-28 | home |
| 109 | 7 | 2024-03-15 | filing_flow |
| 111 | 8 | 2024-03-20 | deductions |
| 104 | 3 | 2024-02-01 | deductions |
| 106 | 5 | 2024-01-31 | filing_flow |
| 102 | 1 | 2024-01-18 | deductions |
| 108 | 2024-03-10 | home | |
| 1 | 23 | 2024-02-22 | help_center |
| 2 | 74 | 2024-02-12 | null |
| 3 | 85 | 2024-04-01 | expenses |
| 4 | 98 | 2024-01-31 | federal_review |
| 5 | 20 | 2024-01-18 | help_center |
| 6 | 67 | 2024-01-29 | profile |
| 7 | 21 | 2024-02-19 | upload_center |
| 8 | 51 | 2024-04-03 | tax_summary |
| 9 | 81 | 2024-01-14 | null |
| 10 | 14 | 2024-01-25 | audit_support |
| 11 | 19 | 2024-02-23 | help_center |
| 12 | 73 | 2024-02-26 | null |
| 13 | 99 | 2024-01-10 | home |
| 14 | 29 | 2024-01-25 | expenses |
| 15 | 32 | 2024-02-25 | null |
| subscription_id | user_id | plan_name | subscription_start_date | subscription_status |
|---|---|---|---|---|
| 210 | 10 | Premium | 2024-05-15 | active |
| 203 | 2 | Premium | 2024-02-20 | active |
| 206 | 5 | Free | 2024-03-05 | cancelled |
| 201 | 3 | Deluxe | 2024-02-02 | active |
| 209 | 9 | Basic | 2024-04-02 | active |
| 202 | 1 | Free | 2024-01-20 | cancelled |
| 205 | 4 | Basic | 2024-03-01 | cancelled |
| 208 | 8 | Premium | 2024-04-07 | active |
| 204 | 4 | Premium | 2024-04-15 | active |
| 207 | 6 | Deluxe | 2023-12-28 | cancelled |
| 1 | 46 | Core | 2024-03-26 | failed_payment |
| 2 | 43 | Starter | 2023-12-27 | paused |
| 3 | 36 | Self-Employed | 2024-03-17 | paused |
| 4 | 7 | Core | 2024-03-25 | processing |
| 5 | 39 | Basic | 2024-04-21 | unknown |
| 6 | 9 | Live Premium | 2024-04-21 | cancelled |
| 7 | 97 | Student | 2024-05-16 | archived |
| 8 | 35 | Live Basic | 2024-04-21 | cancelled |
| 9 | 37 | Federal Only | 2023-12-28 | expired |
| 10 | 69 | null | 2024-02-04 | processing |
| 11 | 34 | Advisor | 2024-05-08 | cancelled |
| 12 | 52 | Deluxe | 2024-05-13 | expired |
| 13 | 53 | Student | 2024-01-15 | failed_payment |
| 14 | 9 | Student | 2024-01-09 | upgraded |
| 15 | 79 | Deluxe | 2024-05-18 | failed_payment |
| 16 | 19 | Premium | 2024-05-09 | failed_payment |
| 17 | 51 | Complete | 2024-01-18 | reactivated |
| signup_month | paid_starts | prior_month_active_users | retained_signup_rate |
|---|---|---|---|
| 2024-01 | 3 | 0 | 0.0000 |
| 2024-02 | 2 | 1 | 0.5000 |
| 2024-03 | 4 | 0 | 0.0000 |
| 2024-04 | 6 | 3 | 0.5000 |
| 2024-05 | 6 | 0 | 0.0000 |
