Find the missed call and call back from the below table using SQL

Question

I have question which I'm not able to solve, can anyone please suggest the approach?

From            To      Start    End
9901234567  8854320145  10:00   10:00
9435678911  7657894335  10:30   10:33
8764091245  9765487678  09:45   09:47
8854320145  9901234567  10:05   10:07
7609123987  8854320145  11:13   11:13
6501928465  7609123987  08:09   08:13

Here from is a phone number to is also a phone number, and the start and the end are the duration of the call. Based on these details, I need to identify a missed call and a callback. How should I approach solving this problem using SQL?

Answer 1

From Oracle 12, you can use MATCH_RECOGNIZE with LEAST and GREATEST:

SELECT "From", "To", "Start", "End"
FROM   (
  SELECT t.*,
         LEAST("From", "To") AS lst,
         GREATEST("From", "To") AS gst
  FROM   table_name t
)
MATCH_RECOGNIZE(
  PARTITION BY lst, gst
  ORDER BY "Start", "End"
  ALL ROWS PER MATCH
  PATTERN ( missed_call callback )
  DEFINE
    missed_call AS  "Start" = "End",
    callback    AS  PREV("From") = "To"
                AND PREV("To") = "From"
                AND "Start" < "End"
);

Which, for the sample data:

CREATE TABLE table_name ("From", "To", "Start", "End") AS
SELECT '9901234567', '8854320145',  '10:00', '10:00' FROM DUAL UNION ALL
SELECT '9435678911', '7657894335',  '10:30', '10:33' FROM DUAL UNION ALL
SELECT '8764091245', '9765487678',  '09:45', '09:47' FROM DUAL UNION ALL
SELECT '8854320145', '9901234567',  '10:05', '10:07' FROM DUAL UNION ALL
SELECT '7609123987', '8854320145',  '11:13', '11:13' FROM DUAL UNION ALL
SELECT '6501928465', '7609123987',  '08:09', '08:13' FROM DUAL UNION ALL
SELECT '1234567890', '1234567890',  '09:00', '09:01' FROM DUAL UNION ALL
SELECT '1234567890', '1234567890',  '09:05', '09:06' FROM DUAL UNION ALL
SELECT '1111111111', '2222222222',  '10:00', '10:01' FROM DUAL UNION ALL
SELECT '2222222222', '1111111111',  '10:05', '10:06' FROM DUAL;

Outputs:

From	To	Start	End
9901234567	8854320145	10:00	10:00
8854320145	9901234567	10:05	10:07

fiddle

Answer 2

I believe you could join the table to itself, linking the from to the to, and vice versa:

SELECT mc.*, cb.*
FROM yourtable mc --MissedCalls
    INNER JOIN yourtable cb --Callbacks
        ON mc.To = cb.From 
        AND mc.From = cb.To
        AND cb.Start > cb.End --A cb/callback happens after a missed call
WHERE mc.Start = mc.end --An mc/MissedCall has the same start and end time

This fails in the event that there were multiple missed calls and call backs for the same from/to to/from. It's not clear if that is present in your data.

To solve for that, creating a new derived table that numbers the calls from 1 to N between two numbers regardless of their ordinality (x calls y or y calls x) allows us to determine if the very next call between two parties is a call back.

WITH call_relationships AS 
(
    --Create a call number for each number-to-number relationship
    SELECT dt.*, 
        row_number() OVER (PARTITION BY From, To ORDER BY Start) as call_number, 
        CASE WHEN Start = End THEN 'mc' as missed_call_indicator
    FROM 
        (
            SELECT From as num1, To as num2, Start, End, CAST('To' AS VARCHAR(4)) as rel FROM yourtable
            UNION ALL
            SELECT To, From, Start, End, 'From' FROM yourtable
        ) dt
)
SELECT mc.num1 as missed_call_from, 
    mc.num2 as missed_call_to, 
    mc.Start as missed_call_time,
    cb.Start as call_back_start_time,
    cb.End as call_back_end_time
FROM call_relationships mc 
    INNER JOIN call_relationships cb 
        ON cb.rel = 'To' --Select only calls where y calls x
        AND mc.num1 = cb.num1
        AND mc.num2 = cb.num2
        AND mc.call_number + 1 = cb.call_number -- only consider the very next call between these two numbers
WHERE mc.missed_call_indicator IS NOT NULL
    and mc.rel = 'From' --Select only calls where x calls y