Task
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.
Schema
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 |
Sample data
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 |
Expected output
| 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 |
