Welcome to the SQL screen.
The question is on your right: Summarize Customer Support Outcomes. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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.
| 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 |