Problem
BrightCart wants to measure how efficiently each acquisition channel converts users across customer segments. Write a PostgreSQL query to calculate conversion rate by channel and segment for users who signed up in January 2024.
A user is considered converted if they have at least one row in conversions with is_conversion = true within 30 days of their signup date.
Requirements
- Return one row per
channelandsegment. - Count total signed-up users and converted users.
- Compute conversion rate as
converted_users / total_users, rounded to 4 decimal places. - Exclude users whose segment is
NULL. - Order results by conversion rate descending, then by channel and segment ascending.
Table Definitions
users
| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| channel | VARCHAR(50) | Acquisition channel |
| segment | VARCHAR(50) | Customer segment |
conversions
| column | type | description |
|---|---|---|
| conversion_id | INT | Unique conversion event identifier |
| user_id | INT | User tied to the conversion event |
| conversion_date | DATE | Date of conversion event |
| is_conversion | BOOLEAN | Whether the event counts as a conversion |
channels
| column | type | description |
|---|---|---|
| channel | VARCHAR(50) | Channel name |
| channel_group | VARCHAR(50) | Higher-level channel grouping |
Schema
users
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| channel | VARCHAR(50) | Acquisition channel for the signup |
| segment | VARCHAR(50) | Customer segment |
conversions
| Column | Type | Description |
|---|---|---|
| conversion_idPK | INT | Unique conversion event identifier |
| user_id | INT | User associated with the event |
| conversion_date | DATE | Date the conversion event occurred |
| is_conversion | BOOLEAN | Whether the event should count as a conversion |
channels
| Column | Type | Description |
|---|---|---|
| channelPK | VARCHAR(50) | Channel name |
| channel_group | VARCHAR(50) | Higher-level grouping of the channel |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
