https://www.youtube.com/watch?v=lxFQ0RgyEcA&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=12

-- Creating call_start_logs table
CREATE TABLE call_start_logs (
    phone_number VARCHAR(10),
    start_time DATETIME
);

-- Inserting records into call_start_logs
INSERT INTO call_start_logs (phone_number, start_time) VALUES
    ('PN1', '2022-01-01 10:20:00'),
    ('PN1', '2022-01-01 16:25:00'),
    ('PN2', '2022-01-01 12:30:00'),
    ('PN3', '2022-01-02 10:00:00'),
    ('PN3', '2022-01-02 12:30:00'),
    ('PN3', '2022-01-03 09:20:00');

-- Creating call_end_logs table
CREATE TABLE call_end_logs (
    phone_number VARCHAR(10),
    end_time DATETIME
);

-- Inserting records into call_end_logs
INSERT INTO call_end_logs (phone_number, end_time) VALUES
    ('PN1', '2022-01-01 10:45:00'),
    ('PN1', '2022-01-01 17:05:00'),
    ('PN2', '2022-01-01 12:55:00'),
    ('PN3', '2022-01-02 10:20:00'),
    ('PN3', '2022-01-02 12:50:00'),
    ('PN3', '2022-01-03 09:40:00');

ROW_NUMBER()

-- Approach 1
SELECT A.phone_number,A.start_time,B.end_time, TIMESTAMPDIFF(minute,start_time,end_time) AS duration
FROM

(SELECT *,row_number() OVER (PARTITION BY phone_number ORDER BY start_time) AS Start_seq
FROM call_start_logs) A

INNER JOIN

(SELECT *,row_number() OVER (PARTITION BY phone_number ORDER BY end_time) AS End_seq
FROM call_end_logs) B
ON A.phone_number = B.phone_number AND A.start_seq=B.end_seq

TIMESTAMPDIFF()
SUBQUERY
UNION_UNION ALL

-- Approach 2

SELECT phone_number,min(call_time) AS start_time,Max(call_time) AS end_time,timestampdiff(minute,min(call_time),max(call_time)) AS Duration FROM
(
SELECT phone_number,start_time AS call_time,ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY start_time) AS rn FROM call_start_logs 
UNION ALL
SELECT *,ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY end_time) AS rn FROM call_end_logs
) A
GROUP BY phone_number,rn