Auto-Tagging Support Tickets
Problem
A help desk keeps a category dictionary in `category_terms` with columns `(category_id, term)` (the pair `(category_id, term)` is unique) and incoming messages in `tickets` with columns `(ticket_id, message)` (`ticket_id` is the primary key).
Tag each ticket by category. A ticket belongs to a category if at least one of that category's terms appears in the message as a whole word, matched case-insensitively (so 'vpn' matches 'VPN' but not 'envpn'). For each ticket report `ticket_id` and `category`: the matching category ids joined by commas and sorted ascending. If no term matches, `category` is the literal text 'Unclassified'. Return rows ordered by `ticket_id`.
Tables
Example rows — the live problem includes the full dataset.
| category_id | term |
|---|---|
| 1 | refund |
| 1 | billing |
| 3 | crash |
| ticket_id | message |
|---|---|
| 1 | I need a refund for my order |
| 2 | the billing and refund both wrong |
| 3 | app keeps crash and login fails |
Expected output
Your answer should return 4 rows with the columns ticket_id, category.
Starter code (SQL)
SELECT *
FROM category_terms;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock