Your question is Aggregate Support Ticket Themes. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
A support analytics team at Databricks wants a quick rollup of recurring ticket themes for April 2024. Write a SQL query to identify themes that appeared in at least 2 distinct tickets among tickets that were either open or resolved.
2024-04-01 through 2024-04-30.open or resolved.theme_name is not null and confidence_score >= 0.75.theme_nameticket_count: number of distinct tickets with that themehigh_priority_count: number of distinct tickets with priority P1 or P2ticket_count >= 2.ticket_count descending, then high_priority_count descending, then theme_name ascending.| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique support ticket identifier |
| customer_id | INT | Customer account identifier |
| created_at | DATE | Date the support ticket was created |
| status | VARCHAR(20) | Current ticket status |
| priority | VARCHAR(10) | Ticket priority such as P1 or P2 |
| product_area | VARCHAR(50) | Databricks product area mentioned in the ticket |
| Column | Type | Description |
|---|---|---|
| theme_idPK | INT | Unique extracted theme row identifier |
| ticket_id | INT | Related support ticket identifier |
| theme_name | VARCHAR(100) | Theme extracted from ticket text by a PySpark pipeline |
| confidence_score | DECIMAL(4,2) | Confidence score for the extracted theme |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Customer account identifier |
| account_name | VARCHAR(100) | Customer account name |
| segment | VARCHAR(30) | Customer segment |