Support Ticket Resolution Stats
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of support tickets. Each ticket has a priority level ('high', 'medium', or 'low'), a status ('resolved' or 'open'), a creation date, and an optional resolved date. Write a SQL query to find for each month and priority: the total number of tickets, the number of resolved tickets, the number of open tickets, and the resolution rate as a percentage (resolved / total × 100, rounded to 2 decimal places). Return the result ordered by month and priority. Table: Tickets
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| priority | VARCHAR | 'high', 'medium', or 'low' |
| status | VARCHAR | 'resolved' or 'open' |
| created_date | DATE | Date the ticket was opened |
| resolved_date | DATE | Date resolved (NULL if still open) |
Database Schema (Inferred)
Tickets
| Column Name | Example Value |
|---|---|
| id | 1 |
| priority | high |
| status | resolved |
| created_date | 2023-04-02 |
| resolved_date | 2023-04-03 |
Example
Tickets
| id | priority | status | created_date | resolved_date |
|---|---|---|---|---|
| 1 | high | resolved | 2023-04-02 | 2023-04-03 |
| 2 | high | open | 2023-04-10 | NULL |
| 3 | medium | resolved | 2023-04-15 | 2023-04-18 |
| 4 | low | resolved | 2023-04-20 | 2023-04-25 |
| 5 | high | resolved | 2023-05-01 | 2023-05-02 |
| 6 | medium | open | 2023-05-12 | NULL |
Output
| month | priority | total_tickets | resolved_count | open_count | resolution_rate |
|---|---|---|---|---|---|
| 2023-04 | high | 2 | 1 | 1 | 50 |
| 2023-04 | low | 1 | 1 | 0 | 100 |
| 2023-04 | medium | 1 | 1 | 0 | 100 |
| 2023-05 | high | 1 | 1 | 0 | 100 |
| 2023-05 | medium | 1 | 0 | 1 | 0 |
Explanation:
Group by month and priority. Use CASE WHEN to count resolved and open separately. Resolution rate = resolved_count / total_tickets * 100.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Tickets
| id | priority | status | created_date | resolved_date |
|---|---|---|---|---|
| 1 | high | resolved | 2023-04-02 | 2023-04-03 |
| 2 | high | open | 2023-04-10 | NULL |
| 3 | medium | resolved | 2023-04-15 | 2023-04-18 |
| 4 | low | resolved | 2023-04-20 | 2023-04-25 |
| 5 | high | resolved | 2023-05-01 | 2023-05-02 |
| 6 | medium | open | 2023-05-12 | NULL |
Output
| month | priority | total_tickets | resolved_count | open_count | resolution_rate |
|---|---|---|---|---|---|
| 2023-04 | high | 2 | 1 | 1 | 50 |
| 2023-04 | low | 1 | 1 | 0 | 100 |
| 2023-04 | medium | 1 | 1 | 0 | 100 |
| 2023-05 | high | 1 | 1 | 0 | 100 |
| 2023-05 | medium | 1 | 0 | 1 | 0 |