Number Port Requests Breaching the 72-Hour SLA

Medium
514.

Number Port Requests Breaching the 72-Hour SLA

MediumJoinsDates

MySQL

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

ColumnType
operator_idint
operator_namevarchar

Primary key: operator_id.

Table: PortRequest

ColumnTypeValues
request_idint
mobile_numbervarchar
donor_idint
recipient_idint
requested_atdatetime
completed_atdatetime
statusenumcompleted, 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.

Example

The rows must come back in the order shown.

Operator

operator_idoperator_name
1Nimbus Mobile
2Tara Telecom
3Vega Wireless

PortRequest

request_idmobile_numberdonor_idrecipient_idrequested_atcompleted_atstatus
19845012345122024-09-20 10:00:002024-09-22 18:00:00completed
29900123456212024-09-21 09:00:002024-09-24 10:00:00completed
39731122334132024-09-25 12:00:00NULLpending
48123456789312024-09-28 08:00:00NULLpending
57019988776122024-09-10 11:00:00NULLrejected
69611223344232024-09-15 06:00:002024-09-18 06:00:00completed
79886655443322024-09-26 00:00:00NULLpending

Output

request_idmobile_numberdonorrecipienthours_open
39731122334Nimbus MobileVega Wireless108
79886655443Vega WirelessTara Telecom96
29900123456Tara TelecomNimbus Mobile73
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.

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

ColumnType
operator_idint
operator_namevarchar

Primary key: operator_id.

Table: PortRequest

ColumnType
request_idint
mobile_numbervarchar
donor_idint
recipient_idint
requested_atdatetime
completed_atdatetime
statusenum(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_idoperator_name
1Nimbus Mobile
2Tara Telecom
3Vega Wireless

PortRequest

request_idmobile_numberdonor_idrecipient_idrequested_atcompleted_atstatus
19845012345122024-09-20 10:00:002024-09-22 18:00:00completed
29900123456212024-09-21 09:00:002024-09-24 10:00:00completed
39731122334132024-09-25 12:00:00NULLpending
48123456789312024-09-28 08:00:00NULLpending
57019988776122024-09-10 11:00:00NULLrejected
69611223344232024-09-15 06:00:002024-09-18 06:00:00completed
79886655443322024-09-26 00:00:00NULLpending

Output

request_idmobile_numberdonorrecipienthours_open
39731122334Nimbus MobileVega Wireless108
79886655443Vega WirelessTara Telecom96
29900123456Tara TelecomNimbus Mobile73

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_id

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