Your question is Conversion Rate by Channel Segment. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
channel and segment.converted_users / total_users, rounded to 4 decimal places.NULL.| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| channelPK | VARCHAR(50) | Channel name |
| channel_group | VARCHAR(50) | Higher-level grouping of the channel |