Urgent Support Tickets Waiting More Than a Day — SQL Problem
The support desk's morning shift starts at 2025-03-10 09:00:00.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #323
Problem statement
The support desk's morning shift starts at 2025-03-10 09:00:00. Before handing over, the night lead lists every urgent ticket that is still unanswered and has been waiting more than 24 hours — created strictly before 2025-03-09 09:00:00. A ticket created exactly 24 hours earlier has not crossed the line yet.
Return the tickets with priority 'urgent' and status 'open' or 'pending' that were created more than 24 hours before the shift change, with the columns ticket_id, subject and created_at, ordered by created_at, then ticket_id.
Tables
Table: SupportTicket
| Column | Type |
|---|---|
| ticket_id | int |
| subject | varchar |
| priority | enum(low, normal, high, urgent) |
| status | enum(open, pending, resolved, closed) |
| created_at | datetime |
Primary key: ticket_id.
One row per customer ticket. 'pending' means the desk is waiting on another team; resolved and closed tickets need no action.
Examples
Example 1
SupportTicket
| ticket_id | subject | priority | status | created_at |
|---|---|---|---|---|
| 1001 | UPI payment debited but order failed | urgent | open | 2025-03-08 22:15:00 |
| 1002 | Cannot log in after update | urgent | pending | 2025-03-07 11:40:00 |
| 1003 | Refund not received | high | open | 2025-03-07 09:05:00 |
| 1004 | OTP not delivered | urgent | resolved | 2025-03-06 18:30:00 |
| 1005 | Account locked | urgent | open | 2025-03-09 09:00:00 |
| 1006 | App crashes on launch | urgent | open | 2025-03-10 02:10:00 |
| 1007 | GST invoice shows wrong amount | urgent | open | 2025-03-08 22:15:00 |
Output
| ticket_id | subject | created_at |
|---|---|---|
| 1002 | Cannot log in after update | 2025-03-07 11:40:00 |
| 1001 | UPI payment debited but order failed | 2025-03-08 22:15:00 |
| 1007 | GST invoice shows wrong amount | 2025-03-08 22:15:00 |
How to solve Urgent Support Tickets Waiting More Than a Day
This is a filter with three conditions joined by AND. The priority must be 'urgent', the status one of the two that still need work — IN ('open', 'pending') reads better than two ORs — and the ticket must have been waiting longer than a day at the shift change.
The time condition is easiest to write by moving the boundary instead of the column: waiting more than 24 hours at 2025-03-10 09:00:00 means created before 2025-03-09 09:00:00, so created_at < '2025-03-09 09:00:00' does it and can use an index on created_at. The comparison is strict, so the ticket created on the boundary stays out. Computing the age per row with TIMESTAMPDIFF(SECOND, created_at, …) > 86400 gives the same rows but must evaluate every ticket — fine here, slower on a large table. Be careful with TIMESTAMPDIFF(HOUR, …) >= 24: it truncates to whole hours, so 24 hours and 0 seconds and 24 hours 59 minutes look alike, and the boundary ticket would wrongly come back.
Two tickets can share a creation time, so the order needs ticket_id as a second key to be fixed.
Reference solution (MySQL)
SELECT ticket_id, subject, created_at
FROM SupportTicket
WHERE priority = 'urgent'
AND status IN ('open', 'pending')
AND created_at < '2025-03-09 09:00:00'
ORDER BY created_at, ticket_idAnother way
SELECT ticket_id, subject, created_at FROM SupportTicket
WHERE priority = 'urgent' AND status NOT IN ('resolved', 'closed')
AND TIMESTAMPDIFF(SECOND, created_at, '2025-03-10 09:00:00') > 86400
ORDER BY created_at, ticket_id← Daily Sorting-Hub Backlog Including Days With None · Open Ticket Load per Support Agent →