Task
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
channel and segment.
- 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 |
| | |
Sample Data
users
| user_id | signup_date | channel | segment |
|---|
| 1 | 2024-01-05 | Paid Search | SMB |
| 2 | 2024-01-07 | Organic Search | Enterprise |
| 3 | 2024-01-10 | Paid Social | SMB |
| 4 | 2024-01-12 | Email | Mid-Market |
| | | |
conversions
| conversion_id | user_id | conversion_date | is_conversion |
|---|
| 101 | 1 | 2024-01-20 | true |
| 102 | 1 | 2024-02-02 | true |
| 103 | 2 | 2024-02-15 | true |
| 104 | 3 | 2024-01-25 | false |
| | | |
Expected Output
| channel | segment | total_users | converted_users | conversion_rate |
|---|
| Email | Mid-Market | 2 | 2 | 1.0000 |
| Organic Search | SMB | 1 | 1 | 1.0000 |
| Paid Search | SMB | 2 | 1 | 0.5000 |
| Paid Social | SMB | 2 | 1 | 0.5000 |
| Organic Search | Enterprise | 2 | 0 | 0.0000 |
