Aa
ANovaCart ran a marketing campaign and wants to know which customer segment converted best. Write a SQL query to identify the highest-converting segment for a specific campaign.
CMP100.customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment |
| signup_date | DATE | Customer signup date |
campaign_sends| column | type | description |
|---|---|---|
| send_id | INT | Unique campaign send record |
| campaign_id | VARCHAR(20) | Campaign identifier |
| customer_id | INT | Targeted customer |
| send_date | DATE | Date the campaign was sent |
orders| column | type | description |
|---|---|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Purchase date |
| order_amount | DECIMAL(10,2) | Order value |
customers| customer_id | customer_name | segment | signup_date |
|---|---|---|---|
| 1 | Ava Patel | New | 2024-01-10 |
| 2 | Ben Ortiz | Loyal | 2023-11-02 |
| 3 | Chloe Kim | At Risk | 2023-08-15 |
| 4 | Daniel Reed | Loyal | 2023-06-20 |
| 5 | Ella Chen | New | 2024-02-01 |
| 6 | Finn Brooks | NULL | 2024-01-25 |
campaign_sends| send_id | campaign_id | customer_id | send_date |
|---|---|---|---|
| 101 | CMP100 | 1 | 2024-03-01 |
| 102 | CMP100 | 2 | 2024-03-01 |
| 103 | CMP100 | 3 | 2024-03-01 |
| 104 | CMP100 | 4 | 2024-03-01 |
| 105 | CMP100 | 5 | 2024-03-01 |
| 106 | CMP100 | 6 | 2024-03-01 |
orders| order_id | customer_id | order_date | order_amount |
|---|---|---|---|
| 1001 | 1 | 2024-03-03 | 45.00 |
| 1002 | 2 | 2024-03-10 | 80.00 |
| 1003 | 3 | 2024-03-05 | 25.00 |
| 1004 | 4 | 2024-03-20 | 60.00 |
| 1005 | 5 | 2024-02-28 | 30.00 |
| 1006 | 6 | 2024-03-04 | 15.00 |
| segment | targeted_customers | converted_customers | conversion_rate_pct | segment_rank |
|---|---|---|---|---|
| At Risk | 2 | 2 | 100.00 | 1 |
| New | 3 | 2 | 66.67 | 2 |
| Loyal | 3 | 1 | 33.33 | 3 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| segment | VARCHAR(50) | Customer marketing segment |
| signup_date | DATE | Customer signup date |
| Column | Type | Description |
|---|---|---|
| send_idPK | INT | Unique campaign send record |
| campaign_id | VARCHAR(20) | Campaign identifier |
| customer_id | INT | Customer who received the campaign |
| send_date | DATE | Date the campaign was sent |
| 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_amount | DECIMAL(10,2) | Order value |
| order_id | customer_id | order_date | order_amount |
|---|---|---|---|
| 1001 | 1 | 2024-03-03 | 45.00 |
| 1002 | 2 | 2024-03-10 | 80.00 |
| 1003 | 3 | 2024-03-05 | 25.00 |
| 1004 | 4 | 2024-03-20 | 60.00 |
| 1005 | 5 | 2024-02-28 | 30.00 |
| 1006 | 6 | 2024-03-04 | 15.00 |
| 1007 | 7 | 2024-03-08 | 40.00 |
| 1008 | 8 | 2024-03-07 | 0.00 |
| 1009 | 8 | 2024-03-15 | 55.00 |
| 1010 | 9 | 2024-03-02 | |
| 1011 | 2024-03-04 | 22.00 | |
| 1012 | 1 | 2024-03-06 | 18.00 |
| 1013 | 9 | 2024-03-01 | -15 |
| 1014 | 5 | 2024-03-12 | -8 |
| 1015 | 3 | 2024-03-15 | 62 |
| 1016 | 8 | 2024-03-10 | 14 |
| 1017 | 8 | 2024-03-20 | 36 |
| 1018 | 8 | 2024-03-22 | 23 |
| 1019 | 4 | 2024-03-09 | 44 |
| 1020 | 7 | 2024-03-23 | -2 |
| 1021 | 8 | 2024-03-18 | 2 |
| 1022 | 5 | 2024-03-19 | 68 |
| 1023 | 1 | 2024-03-11 | -6 |
| 1024 | 4 | 2024-03-11 | 24 |
| 1025 | 6 | 2024-03-02 | -12 |
| customer_id | customer_name | segment | signup_date |
|---|---|---|---|
| 1 | Ava Patel | New | 2024-01-10 |
| 2 | Ben Ortiz | Loyal | 2023-11-02 |
| 3 | Chloe Kim | At Risk | 2023-08-15 |
| 4 | Daniel Reed | Loyal | 2023-06-20 |
| 5 | Ella Chen | New | 2024-02-01 |
| 6 | Finn Brooks | 2024-01-25 | |
| 7 | Grace Miller | At Risk | 2023-07-11 |
| 8 | Hugo Diaz | New | 2024-02-14 |
| 9 | Ivy Nguyen | Loyal | 2023-05-09 |
| 10 | Jack Turner | Dormant | 2022-12-30 |
| 11 | Jack Turner | Dormant | 2024-01-14 |
| 12 | Finn Brooks | Family | 2023-03-16 |
| 13 | Olivia Park | Rural | 2023-10-27 |
| 14 | Yara Hassan | Occasional | 2023-09-07 |
| 15 | Amelia Ross | Recent Buyer | 2023-12-25 |
| 16 | Ryan Cooper | null | 2023-12-06 |
| 17 | Priya Shah | Reactivated | 2023-05-28 |
| 18 | Blake Carter | Reactivated | 2023-01-25 |
| 19 | Xavier Price | Student | 2023-05-24 |
| 20 | Quinn Foster | Suburban | 2023-12-03 |
| 21 | Ava Patel | null | 2024-02-09 |
| 22 | Maya Singh | Discount Seeker | 2023-10-21 |
| 23 | Jack Turner | Dormant | 2024-01-31 |
| 24 | Ben Ortiz | Frequent Buyer | 2023-07-25 |
| 25 | Hugo Diaz | App Engaged | 2023-01-22 |
| 26 | Amelia Ross | App Engaged | 2023-03-29 |
| 27 | Priya Shah | Business | 2024-01-06 |
| send_id | campaign_id | customer_id | send_date |
|---|---|---|---|
| 101 | CMP100 | 4 | 2024-03-01 |
| 102 | CMP100 | 1 | 2024-03-01 |
| 103 | CMP100 | 7 | 2024-03-01 |
| 104 | CMP100 | 2 | 2024-03-01 |
| 105 | CMP100 | 5 | 2024-03-01 |
| 106 | CMP100 | 8 | 2024-03-01 |
| 107 | CMP100 | 3 | 2024-03-01 |
| 108 | CMP100 | 9 | 2024-03-01 |
| 109 | CMP200 | 6 | 2024-03-01 |
| 110 | CMP200 | 10 | 2024-03-01 |
| 111 | CMP114 | 6 | 2024-02-28 |
| 112 | CMP122 | 6 | 2024-03-04 |
| 113 | CMP107 | 10 | 2024-03-03 |
| 114 | CMP112 | 7 | 2024-03-02 |
| 115 | CMP101 | 3 | 2024-03-02 |
| 116 | CMP100 | 8 | 2024-02-29 |
| 117 | CMP124 | 8 | 2024-03-01 |
| 118 | CMP121 | 7 | 2024-02-28 |
| 119 | CMP114 | 8 | 2024-03-01 |
| 120 | CMP102 | 4 | 2024-03-03 |
| 121 | CMP101 | 10 | 2024-02-27 |
| 122 | CMP123 | 6 | 2024-03-04 |
| 123 | CMP118 | 3 | 2024-03-04 |
| 124 | CMP121 | 5 | 2024-02-29 |
| 125 | CMP104 | 9 | 2024-02-28 |
| 126 | CMP104 | 3 | 2024-02-27 |
| 127 | CMP110 | 5 | 2024-03-04 |
| 128 | CMP107 | 8 | 2024-02-28 |
| 129 | CMP121 | 2 | 2024-02-28 |
| 130 | CMP200 | 3 | 2024-02-29 |
| segment | targeted_customers | converted_customers | conversion_rate_pct | segment_rank |
|---|---|---|---|---|
| At Risk | 2 | 2 | 100.00 | 1 |
| New | 3 | 2 | 66.67 | 2 |
| Loyal | 3 | 1 | 33.33 | 3 |
