Number Port Requests Breaching the 72-Hour SLA — SQL Problem
The regulator expects a mobile number port to complete within 72 hours of the request. The compliance report is run as of 2024-09-30 00:00:00.
- Difficulty: Medium
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #514
Problem statement
The regulator expects a mobile number port to complete within 72 hours of the request. The compliance report is run as of 2024-09-30 00:00:00.
For every request that is not 'rejected', the time open is the whole hours from requested_at to completed_at — or, for a request still 'pending' (completed_at NULL), to 2024-09-30 00:00:00. Return the requests open more than 72 hours, with the columns request_id, mobile_number, donor and recipient (the operators' names) and hours_open. Order by hours_open from highest to lowest, then by request_id.
Tables
Table: Operator
| Column | Type |
|---|---|
| operator_id | int |
| operator_name | varchar |
Primary key: operator_id.
Table: PortRequest
| Column | Type |
|---|---|
| request_id | int |
| mobile_number | varchar |
| donor_id | int |
| recipient_id | int |
| requested_at | datetime |
| completed_at | datetime |
| status | enum(completed, pending, rejected) |
Primary key: request_id.
donor_id and recipient_id are operators. completed_at is set only for completed requests; every request was made before the report time.
Examples
Example 1
Operator
| operator_id | operator_name |
|---|---|
| 1 | Nimbus Mobile |
| 2 | Tara Telecom |
| 3 | Vega Wireless |
PortRequest
| request_id | mobile_number | donor_id | recipient_id | requested_at | completed_at | status |
|---|---|---|---|---|---|---|
| 1 | 9845012345 | 1 | 2 | 2024-09-20 10:00:00 | 2024-09-22 18:00:00 | completed |
| 2 | 9900123456 | 2 | 1 | 2024-09-21 09:00:00 | 2024-09-24 10:00:00 | completed |
| 3 | 9731122334 | 1 | 3 | 2024-09-25 12:00:00 | NULL | pending |
| 4 | 8123456789 | 3 | 1 | 2024-09-28 08:00:00 | NULL | pending |
| 5 | 7019988776 | 1 | 2 | 2024-09-10 11:00:00 | NULL | rejected |
| 6 | 9611223344 | 2 | 3 | 2024-09-15 06:00:00 | 2024-09-18 06:00:00 | completed |
| 7 | 9886655443 | 3 | 2 | 2024-09-26 00:00:00 | NULL | pending |
Output
| request_id | mobile_number | donor | recipient | hours_open |
|---|---|---|---|---|
| 3 | 9731122334 | Nimbus Mobile | Vega Wireless | 108 |
| 7 | 9886655443 | Vega Wireless | Tara Telecom | 96 |
| 2 | 9900123456 | Tara Telecom | Nimbus Mobile | 73 |
How to solve Number Port Requests Breaching the 72-Hour SLA
Two independent ideas meet here. First, each request names two operators, so Operator is joined twice under different aliases — d matched on donor_id, c on recipient_id — and each copy contributes one name. A single join could only ever give one of them.
Second, the clock: a completed request is measured to its completion, a pending one to the report time. COALESCE(completed_at, '2024-09-30 00:00:00') picks the right end for both in one expression, and TIMESTAMPDIFF(HOUR, requested_at, end) gives whole hours. Rejected requests are excluded explicitly — they never complete, and without the filter the COALESCE would age them like pending ones. The SLA allows 72 hours, so the breach test is > 72: a port finished in exactly 72 hours is compliant.
The alternative branches on status with a CASE and fetches the names with scalar subqueries, which reads naturally but repeats a lookup per row. Both versions are one pass over the requests with primary-key lookups into Operator.
Reference solution (MySQL)
SELECT r.request_id, r.mobile_number, d.operator_name AS donor, c.operator_name AS recipient,
TIMESTAMPDIFF(HOUR, r.requested_at, COALESCE(r.completed_at, '2024-09-30 00:00:00')) AS hours_open
FROM PortRequest r
JOIN Operator d ON d.operator_id = r.donor_id
JOIN Operator c ON c.operator_id = r.recipient_id
WHERE r.status <> 'rejected'
AND TIMESTAMPDIFF(HOUR, r.requested_at, COALESCE(r.completed_at, '2024-09-30 00:00:00')) > 72
ORDER BY hours_open DESC, r.request_idAnother way
SELECT * FROM (
SELECT r.request_id, r.mobile_number,
(SELECT operator_name FROM Operator o WHERE o.operator_id = r.donor_id) AS donor,
(SELECT operator_name FROM Operator o WHERE o.operator_id = r.recipient_id) AS recipient,
CASE WHEN r.status = 'completed' THEN TIMESTAMPDIFF(HOUR, r.requested_at, r.completed_at)
ELSE TIMESTAMPDIFF(HOUR, r.requested_at, '2024-09-30 00:00:00') END AS hours_open
FROM PortRequest r
WHERE r.status IN ('completed', 'pending')
) x
WHERE hours_open > 72
ORDER BY hours_open DESC, request_id← International Voice Call Share per Caller · Prepaid Service Lapses Between Recharges →