Ola: Service Tickets Breaching the 3-Working-Day SLA
Service centres promise to close a ticket within 3 working days.
- Difficulty: Hard
- Topics: Dates, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #847
Problem statement
Service centres promise to close a ticket within 3 working days. Centres are shut on Sundays and on the dates in Holiday, so the after-sales team counts working days rather than calendar days.
A closed ticket's working days are the days after opened_on up to and including closed_on that are neither a Sunday nor a holiday — a ticket closed the day it opened took 0. A ticket breaches when it took more than 3 working days. For every centre with at least one closed ticket, return centre_name, closed_tickets and breached. Tickets still open are ignored. Order the rows by centre_name.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: ServiceCentre
| Column | Type |
|---|---|
| centre_id | int |
| centre_name | varchar |
Primary key: centre_id.
One row per service centre; names are unique.
Table: ServiceTicket
| Column | Type |
|---|---|
| ticket_id | int |
| centre_id | int |
| opened_on | date |
| closed_on | date |
Primary key: ticket_id.
closed_on is NULL while the ticket is open, and never before opened_on.
Table: Holiday
| Column | Type |
|---|---|
| holiday_on | date |
| holiday_name | varchar |
Primary key: holiday_on.
Dates every centre is closed. A holiday can fall on a Sunday.
Examples
Example 1
ServiceCentre
| centre_id | centre_name |
|---|---|
| 1 | SC Whitefield |
| 2 | SC Hadapsar |
| 3 | SC Madhapur |
ServiceTicket
| ticket_id | centre_id | opened_on | closed_on |
|---|---|---|---|
| 1 | 1 | 2024-08-01 | 2024-08-05 |
| 2 | 1 | 2024-08-13 | 2024-08-17 |
| 3 | 1 | 2024-08-13 | 2024-08-19 |
| 4 | 2 | 2024-08-22 | 2024-08-27 |
| 5 | 2 | 2024-08-20 | NULL |
| 6 | 2 | 2024-08-28 | 2024-08-28 |
| 7 | 3 | 2024-08-05 | 2024-08-12 |
Holiday
| holiday_on | holiday_name |
|---|---|
| 2024-08-15 | Independence Day |
| 2024-08-26 | Janmashtami |
Output
| centre_name | closed_tickets | breached |
|---|---|---|
| SC Hadapsar | 2 | 0 |
| SC Madhapur | 1 | 1 |
| SC Whitefield | 3 | 1 |
How to solve Ola: Service Tickets Breaching the 3-Working-Day SLA
DATEDIFF counts calendar days, but the SLA counts only days the centre is open, and holidays can fall anywhere. So the days between opening and closing have to be materialised and inspected one by one. A recursive CTE does that: the anchor is each closed ticket's opened_on, and the recursive step adds one day while the date is still before closed_on, producing every date from opening to closing inclusive.
Each ticket's expanded dates are then filtered: strictly after opened_on (the opening day is not counted, so a same-day close is 0), not a Sunday (DAYOFWEEK returns 1 for Sunday), and not in Holiday (NOT EXISTS). A holiday on a Sunday is excluded once, not twice, because each date is one row. The filter sits in the LEFT JOIN's ON, so a ticket with no counted days still appears with COUNT(x.d) = 0. Per centre, the closed tickets and those above 3 working days are counted; open tickets never enter.
The alternative builds one shared calendar from the earliest opening to the latest closing, keeps the open days, and counts them per ticket with a correlated subquery. It generates fewer rows when tickets overlap heavily; the per-ticket expansion is simpler to reason about. Both are linear in the total number of days covered.
Reference solution (MySQL)
WITH RECURSIVE ticket_days AS (
SELECT ticket_id, opened_on AS d, closed_on FROM ServiceTicket WHERE closed_on IS NOT NULL
UNION ALL
SELECT ticket_id, DATE_ADD(d, INTERVAL 1 DAY), closed_on FROM ticket_days WHERE d < closed_on
), worked AS (
SELECT t.ticket_id, t.centre_id, COUNT(x.d) AS working_days
FROM ServiceTicket t
LEFT JOIN ticket_days x ON x.ticket_id = t.ticket_id
AND x.d > t.opened_on
AND DAYOFWEEK(x.d) <> 1
AND NOT EXISTS (SELECT 1 FROM Holiday h WHERE h.holiday_on = x.d)
WHERE t.closed_on IS NOT NULL
GROUP BY t.ticket_id, t.centre_id
)
SELECT c.centre_name, COUNT(*) AS closed_tickets,
SUM(CASE WHEN w.working_days > 3 THEN 1 ELSE 0 END) AS breached
FROM worked w
JOIN ServiceCentre c ON c.centre_id = w.centre_id
GROUP BY c.centre_id, c.centre_name
ORDER BY c.centre_nameAnother way
WITH RECURSIVE cal AS (
SELECT MIN(opened_on) AS d, MAX(closed_on) AS last_day FROM ServiceTicket
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY), last_day FROM cal WHERE d < last_day
), open_days AS (
SELECT d FROM cal WHERE DAYOFWEEK(d) <> 1 AND d NOT IN (SELECT holiday_on FROM Holiday)
)
SELECT c.centre_name, COUNT(*) AS closed_tickets,
SUM(CASE WHEN (SELECT COUNT(*) FROM open_days o WHERE o.d > t.opened_on AND o.d <= t.closed_on) > 3 THEN 1 ELSE 0 END) AS breached
FROM ServiceTicket t
JOIN ServiceCentre c ON c.centre_id = t.centre_id
WHERE t.closed_on IS NOT NULL
GROUP BY c.centre_id, c.centre_name
ORDER BY c.centre_name← Ola: First Fault Code After Each Software Install · Paytm: Failed Recharges Still Awaiting a Refund →