Ticket Resolution SLA
ASKED IN INTERVIEWAtlassian
Problem Statement
Support leads need to monitor the efficiency of the High priority queue. Calculate the average number of days it takes to close these critical tickets.
Rules:
- Output columns: average_days_to_close.
- Include only tickets where priority is 'High' and status is 'Closed'.
- Duration = close_date - create_date.
- Round results to 1 decimal place.
Tests your understanding of
Date Arithmetic, Aggregation and Filtering
Input Tables
support_tickets
| ticket_id(INTEGER) | priority(VARCHAR) | status(VARCHAR) | create_date(TIMESTAMP) | close_date(TIMESTAMP) |
|---|---|---|---|---|
| 1 | High | Closed | 2024-01-01 10:00:00 | 2024-01-03 10:00:00 |
| 2 | High | Closed | 2024-01-05 09:00:00 | 2024-01-10 09:00:00 |
| 3 | Medium | Closed | 2024-01-01 12:00:00 | 2024-01-02 12:00:00 |
| 4 | High | Open | 2024-01-10 14:00:00 | null |
| 5 | High | Closed | 2024-01-15 08:00:00 | 2024-01-16 08:00:00 |
| 6 | Low | Closed | 2024-01-10 10:00:00 | 2024-01-20 10:00:00 |
Expected Output
| average_days_to_close(DECIMAL) |
|---|
| 2.7 |
Tags
MediumASKED IN INTERVIEWDate ArithmeticAggregationFiltering
15-20 min
54%