🚨 Every Minute a Ticket Sits Open, Trust Erodes.
At CRED's customer support operations centre, unresolved tickets are a time bomb. SLA policy states that any ticket open for more than 3 days without resolution is considered escalated.
Find all tickets that have been open for more than 3 days and are still unresolved (status = 'open'). Return ticket_id, customer_id, subject, days_open (as an integer), and priority. Order by days_open descending.
Use JULIANDAY(CURRENT_DATE) - JULIANDAY(created_at) to compute days open in SQLite. Cast to INTEGER.
tickets Table:
| Column | Type |
|---|---|
| ticket_id | integer |
| customer_id | integer |
| subject | text |
| status | text |
| priority | text |
| created_at | text |
Expected Outcome (Illustrative Sample):
| ticket_id | customer_id | subject | days_open | priority |
|---|---|---|---|---|
| 6 | 106 | Reward points not showing | 25 | low |
The escalation list determines who gets a call from the VP of Support.