Task
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.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| segment | VARCHAR(50) | Customer segment such as New, Existing, or At-Risk |
| signup_date | DATE | Date the customer signed up |
channel_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| customer_id | INT | Customer tied to the channel touchpoint |
| channel_name | VARCHAR(50) | Acquisition or engagement channel |
| event_date | DATE | Date of the channel event |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Order date |
| order_status | VARCHAR(20) | Order status |
| order_amount | DECIMAL(10,2) | Order amount |
Sample data
customers
| customer_id | segment | signup_date |
|---|
| 1 | New | 2024-01-03 |
| 2 | Existing | 2023-11-10 |
| 3 | At-Risk | 2023-06-15 |
channel_events
| event_id | customer_id | channel_name | event_date |
|---|
| 101 | 1 | MyDISH App | 2024-01-05 |
| 102 | 2 | dish.com | 2024-01-08 |
| 103 | 3 | Retail Store | 2024-02-11 |
orders
| order_id | customer_id | order_date | order_status | order_amount |
|---|
| 1001 | 1 | 2024-01-20 | completed | 120.00 |
| 1002 | 2 | 2024-02-14 | cancelled | 80.00 |
| 1003 | 3 | 2024-03-02 | completed | 60.00 |
Expected output
| segment | channel_name | customer_count | converted_customers | total_revenue | avg_order_value | conversion_rate |
|---|
| Existing | dish.com | 2 | 2 | 350.00 | 116.67 | 1.0000 |
| New | MyDISH App | 2 | 1 | 120.00 | 120.00 | 0.5000 |
| At-Risk | Retail Store | 2 | 1 | 60.00 | 60.00 | 0.5000 |
