Your question is Top Channels by Conversion Rate. Start with the requirements and the four 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.
Rocket's Marketing Analytics team wants to evaluate acquisition performance for the Rocket App. Write a PostgreSQL query that identifies the top three marketing channels by monthly conversion rate.
session_at timestamp.ROW_NUMBER(), ordering by conversion rate descending, then session volume descending, then channel name ascending.| Column | Type | Description |
|---|---|---|
| channel_idPK | INT | Marketing channel identifier |
| channel_name | VARCHAR(100) | Marketing channel name |
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Campaign identifier |
| channel_id | INT | Channel associated with the campaign |
| campaign_name | VARCHAR(150) | Campaign name |
| Column | Type | Description |
|---|---|---|
| session_idPK | BIGINT | Rocket App visitor session identifier |
| campaign_id | INT | Attributed campaign identifier |
| session_at | TIMESTAMPTZ | Session start timestamp |
| Column | Type | Description |
|---|---|---|
| conversion_idPK | BIGINT | Conversion event identifier |
| session_id | BIGINT | Session that generated the conversion |
| converted_at | TIMESTAMPTZ | Conversion event timestamp |
| conversion_type | VARCHAR(50) | Type of conversion event |