Task
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.
Schema
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 |
Sample data
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 |
Expected output
| signup_month | paid_starts | prior_month_active_users | retained_signup_rate |
|---|
| 2024-02 | 2 | 2 | 1.0000 |
| 2024-04 | 3 | 2 | 0.6667 |
