Task
At NovaNote, the growth team wants to compare users acquired through ChatGPT against users from all other acquisition channels. Write a PostgreSQL query to produce a cohort-style comparison for users who signed up in January 2024.
Requirements
- Classify each January signup into one of two segments:
ChatGPT if acquisition_channels.channel_name = 'ChatGPT'Other for every other non-null channel
- For each segment, return:
- total signed up users
- activated users (at least one activation event within 7 days of signup)
- purchasers (at least one paid order within 30 days of signup)
- activation rate and purchase rate as percentages rounded to 2 decimals
- Exclude users whose
channel_id is NULL
- Order results by segment name
Tables
users
| column | type | description |
|---|
| user_id | INT | Primary key |
| signup_date | DATE | User signup date |
| channel_id | INT | Acquisition channel |
| country_code | VARCHAR(2) | User country |
| | |
acquisition_channels
| column | type | description |
|---|
| channel_id | INT | Primary key |
| channel_name | VARCHAR(50) | Marketing or referral source |
| | |
events
| column | type | description |
|---|
| event_id | INT | Primary key |
| user_id | INT | User tied to the event |
| event_name | VARCHAR(50) | Product event name |
| event_date | DATE | Event date |
| | |
orders
| column | type | description |
|---|
| order_id | INT | Primary key |
| user_id | INT | Purchasing user |
| order_date | DATE | Order date |
| amount_usd | DECIMAL(10,2) | Paid amount |
| | |
Sample data
Representative rows are included below in the dataset.
Expected output
| segment | signed_up_users | activated_users | purchasers_30d | activation_rate_pct | purchase_rate_pct |
|---|
| ChatGPT | 4 | 3 | 2 | 75.00 | 50.00 |
| Other | 5 | 3 | 2 | 60.00 | 40.00 |
