Urgent Support Tickets Waiting More Than a Day

Easy
323.

Urgent Support Tickets Waiting More Than a Day

EasyBasicsDates

MySQL

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

ColumnTypeValues
ticket_idint
subjectvarchar
priorityenumlow, normal, high, urgent
statusenumopen, pending, resolved, closed
created_atdatetime

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.

Example

The rows must come back in the order shown.

SupportTicket

ticket_idsubjectprioritystatuscreated_at
1001UPI payment debited but order failedurgentopen2025-03-08 22:15:00
1002Cannot log in after updateurgentpending2025-03-07 11:40:00
1003Refund not receivedhighopen2025-03-07 09:05:00
1004OTP not deliveredurgentresolved2025-03-06 18:30:00
1005Account lockedurgentopen2025-03-09 09:00:00
1006App crashes on launchurgentopen2025-03-10 02:10:00
1007GST invoice shows wrong amounturgentopen2025-03-08 22:15:00

Output

ticket_idsubjectcreated_at
1002Cannot log in after update2025-03-07 11:40:00
1001UPI payment debited but order failed2025-03-08 22:15:00
1007GST invoice shows wrong amount2025-03-08 22:15:00
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ticket_idint
subjectvarchar
priorityenum(low, normal, high, urgent)
statusenum(open, pending, resolved, closed)
created_atdatetime

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_idsubjectprioritystatuscreated_at
1001UPI payment debited but order failedurgentopen2025-03-08 22:15:00
1002Cannot log in after updateurgentpending2025-03-07 11:40:00
1003Refund not receivedhighopen2025-03-07 09:05:00
1004OTP not deliveredurgentresolved2025-03-06 18:30:00
1005Account lockedurgentopen2025-03-09 09:00:00
1006App crashes on launchurgentopen2025-03-10 02:10:00
1007GST invoice shows wrong amounturgentopen2025-03-08 22:15:00

Output

ticket_idsubjectcreated_at
1002Cannot log in after update2025-03-07 11:40:00
1001UPI payment debited but order failed2025-03-08 22:15:00
1007GST invoice shows wrong amount2025-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_id

Another 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 →