SQL Query to Analyze Call Durations

SQL Query to calculate call durations from two separate tables:

call_start_logs and call_end_logs.

Here's the breakdown:

✔️ Goal: Identify the duration of each call and duration by matching the start and end times based on phone numbers.

✔️ Approach:
Row Number :
Use ROW_NUMBER() to assign a unique identifier (rn_1 and rn_2) to each record for start_time and end_time within the same phone_number.
Joining :
Match start_time and end_time records based on phone_number and their respective row numbers (rn_1 = rn_2).
Duration Calculation:
Use DATEDIFF(MINUTE, start_time, end_time) to calculate the call duration.

Here’s the query:

select st.phone_number,
st.start_time,
et.end_time,
st.rn_1 as row_no,
datediff(minute,start_time,end_time) as duration
from
(select *,
row_number() over(partition by phone_number order by start_time) as rn_1 from call_start_logs) st
inner join
(select *,
row_number() over(partition by phone_number order by end_time) as rn_2 from call_end_logs) et
on st.phone_number = et.phone_number and
st.rn_1=et.rn_2;

✔️ Use Case: This is particularly useful for telecom or customer service logs where call tracking is required.