Ola: Service Tickets Breaching the 3-Working-Day SLA

Hard
847.

Ola: Service Tickets Breaching the 3-Working-Day SLA

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

ColumnType
centre_idint
centre_namevarchar

Primary key: centre_id.

One row per service centre; names are unique.

Table: ServiceTicket

ColumnType
ticket_idint
centre_idint
opened_ondate
closed_ondate

Primary key: ticket_id.

closed_on is NULL while the ticket is open, and never before opened_on.

Table: Holiday

ColumnType
holiday_ondate
holiday_namevarchar

Primary key: holiday_on.

Dates every centre is closed. A holiday can fall on a Sunday.

Example

The rows must come back in the order shown.

ServiceCentre

centre_idcentre_name
1SC Whitefield
2SC Hadapsar
3SC Madhapur

ServiceTicket

ticket_idcentre_idopened_onclosed_on
112024-08-012024-08-05
212024-08-132024-08-17
312024-08-132024-08-19
422024-08-222024-08-27
522024-08-20NULL
622024-08-282024-08-28
732024-08-052024-08-12

Holiday

holiday_onholiday_name
2024-08-15Independence Day
2024-08-26Janmashtami

Output

centre_nameclosed_ticketsbreached
SC Hadapsar20
SC Madhapur11
SC Whitefield31
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.

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

ColumnType
centre_idint
centre_namevarchar

Primary key: centre_id.

One row per service centre; names are unique.

Table: ServiceTicket

ColumnType
ticket_idint
centre_idint
opened_ondate
closed_ondate

Primary key: ticket_id.

closed_on is NULL while the ticket is open, and never before opened_on.

Table: Holiday

ColumnType
holiday_ondate
holiday_namevarchar

Primary key: holiday_on.

Dates every centre is closed. A holiday can fall on a Sunday.

Examples

Example 1

ServiceCentre

centre_idcentre_name
1SC Whitefield
2SC Hadapsar
3SC Madhapur

ServiceTicket

ticket_idcentre_idopened_onclosed_on
112024-08-012024-08-05
212024-08-132024-08-17
312024-08-132024-08-19
422024-08-222024-08-27
522024-08-20NULL
622024-08-282024-08-28
732024-08-052024-08-12

Holiday

holiday_onholiday_name
2024-08-15Independence Day
2024-08-26Janmashtami

Output

centre_nameclosed_ticketsbreached
SC Hadapsar20
SC Madhapur11
SC Whitefield31

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_name

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