Your question is Compare Segment Performance Across Channels. 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.
You are given customer, order, and channel activity data from DISH digital touchpoints. Write a PostgreSQL query that compares customer behavior across customer segments and acquisition channels for Q1 2024. Return one row per segment and channel_name, including the number of customers in that segment-channel combination, how many placed at least one completed order in Q1, total completed order revenue, average completed order value, and the conversion rate as the share of customers with at least one completed order. Only include segment-channel groups with at least 2 customers, and sort the results by conversion rate descending, then total revenue descending.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| segment | VARCHAR(50) | Customer segment classification |
| signup_date | DATE | Date the customer signed up |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique channel event identifier |
| customer_id | INT | Customer associated with the event |
| channel_name | VARCHAR(50) | Channel where the customer interacted |
| event_date | DATE | Date of the channel event |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(20) | Order lifecycle status |
| order_amount | DECIMAL(10,2) | Order amount |