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.
ChatGPT if acquisition_channels.channel_name = 'ChatGPT'Other for every other non-null channelchannel_id is NULLusers| 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 |
Representative rows are included below in the dataset.
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user created an account |
| channel_id | INT | Acquisition channel tied to the signup |
| country_code | VARCHAR(2) | Two-letter country code |
| Column | Type | Description |
|---|---|---|
| channel_idPK | INT | Unique channel identifier |
| channel_name | VARCHAR(50) | Name of the acquisition channel |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(50) | Type of event |
| event_date | DATE | Date the event occurred |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| user_id | INT | User who placed the order |
| order_date | DATE | Date the order was placed |
| amount_usd | DECIMAL(10,2) | Order amount in USD |
{"users":[["101","2024-01-12","1","US"],["102","2024-01-05","2","CA"],["103","2024-01-20","1","GB"],["104","2024-01-25","3","US"],["105","2024-01-08","4","IN"],["106","2024-01-30","1","US"],["107","2024-01-18","2","DE"],["108","2024-01-03","null","US"],["109","2024-02-02","1","FR"],["110","2024-01-28","5","BR"],["111","2024-01-15","1","US"],["112","2024-01-09","3","AU"],["113","2024-01-23","1","BE"],["114","2024-01-06","3","null"],["115","2024-02-01","1","CH"],["116","2024-02-04","4","CA"],["117Output[["ChatGPT","9","4","3","44.44","33.33"],["Other","14","2","3","14.29","21.43"]]