You are given ACME House customer support and order data and asked to produce a concise dataset summary that could support an analyst’s explanation of a complex analysis. Write a PostgreSQL query that returns one row per customer segment for tickets created in January 2024, showing how many tickets were opened, how many unique customers submitted them, the average resolution time in hours for resolved tickets, and the share of tickets that were tied to an order placed within 30 days before the ticket. Only keep segments with at least 2 tickets, and sort the output by ticket count descending and segment name.
customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment in ACME House CRM |
| signup_date | DATE | Customer signup date |
orders| column | type | description |
|---|---|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Order date |
| order_total | DECIMAL(10,2) | Order amount |
support_tickets| column | type | description |
|---|---|---|
| ticket_id | INT | Unique support ticket identifier |
| customer_id | INT | Customer who opened the ticket |
| created_at | TIMESTAMP | Ticket creation timestamp |
| resolved_at | TIMESTAMP | Ticket resolution timestamp |
| channel | VARCHAR(30) | ACME House support channel |
| issue_type | VARCHAR(50) | Ticket issue category |
customers| customer_id | customer_name | segment | signup_date |
|---|---|---|---|
| 1 | Ava Patel | Enterprise | 2023-06-10 |
| 2 | Liam Chen | SMB | 2023-08-22 |
| 3 | Noah Rivera | Enterprise | 2023-11-05 |
| 4 | Emma Brooks | Self-Serve | 2024-01-03 |
support_tickets| ticket_id | customer_id | created_at | resolved_at | channel | issue_type |
|---|---|---|---|---|---|
| 101 | 1 | 2024-01-05 09:00:00 | 2024-01-05 15:00:00 | ACME House Inbox | Delivery Delay |
| 102 | 2 | 2024-01-07 10:30:00 | 2024-01-08 09:30:00 | ACME House Chat | Billing |
| 103 | 1 | 2024-01-20 14:00:00 | NULL | ACME House Inbox | Damaged Item |
| 104 | 4 | 2024-01-25 08:00:00 | 2024-01-25 12:00:00 | ACME House Chat | Return Request |
| segment | ticket_count | unique_customers | avg_resolution_hours | pct_with_recent_order |
|---|---|---|---|---|
| Enterprise | 4 | 2 | 11.00 | 75.00 |
| SMB | 3 | 2 | 23.50 | 66.67 |
| Self-Serve | 2 | 2 | 14.00 | 50.00 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| segment | VARCHAR(50) | Customer segment used in ACME House reporting |
| signup_date | DATE | Date the customer signed up |
| 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_total | DECIMAL(10,2) | Total order value |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique support ticket identifier |
| customer_id | INT | Customer who opened the ticket |
| created_at | TIMESTAMP | Ticket creation timestamp |
| resolved_at | TIMESTAMP | Ticket resolution timestamp |
| channel | VARCHAR(30) | Support intake channel |
| issue_type | VARCHAR(50) | Category of support issue |
{"orders":[[201,1,"2023-12-20","250.00"],[202,1,"2024-01-18","180.00"],[203,2,"2023-12-10","90.00"],[204,2,"2024-01-06","120.00"],[205,3,"2023-11-30","300.00"],[206,4,"2024-01-01","75.00"],[207,5,"2024-01-15",null],[208,6,"2023-12-01","40.00"],[209,8,"2024-02-03","500.00"],[210,9,"2023-12-31","0.00"],["1","30","2023-12-30","270"],["2","99","2024-01-17","579"],["3","98","2024-01-03","5"],["4","20","2024-01-02","71"],["5","3","2023-12-09","243"],["6","9","2023-12-21","126"],["7","52","2024-01-13",Output[["Enterprise","4","3","14.67","50.00"],["SMB","4","4","-83.16","75.00"],["Self-Serve","2","2","14.00","50.00"]]