Calculate overlap time in seconds for groups in SQL

I have a bunch of timestamps grouped by ID and type in the sample data shown below.

I would like to find overlapped time between start_time and end_time columns in seconds for each group of ID and between each lead and follower combinations. I would like to show the overlap time only for the first record of each group which will always be the “lead” type.

For example, for the ID 1, the follower’s start and end times in row 3 overlap with the lead’s in row 1 for 193 seconds (from 09:00:00 to 09:03:13). the follower’s times in row 3 also overlap with the lead’s in row 2 for 133 seconds (09:01:00 to 2020-05-07 09:03:13). That’s a total of 326 seconds (193+133)

I used the partition clause to rank rows by ID and type and order them by start_time as a start.

How do I get the overlap column?

row# ID type      start_time             end_time               rank. overlap
1    1  lead      2020-05-07 09:00:00    2020-05-07 09:03:34       1  326
2    1  lead      2020-05-07 09:01:00    2020-05-07 09:03:13       2  
3    1  follower  2020-05-07 08:59:00    2020-05-07 09:03:13       1
4    2  lead      2020-05-07 11:23:00    2020-05-07 11:33:00       1  540
4    2  follower  2020-05-07 11:27:00    2020-05-07 11:32:00       1
5    3  lead      2020-05-07 14:45:00    2020-05-07 15:00:00       1  305
6    3  follower  2020-05-07 14:44:00    2020-05-07 14:44:45       1    
7    3  follower  2020-05-07 14:50:00    2020-05-07 14:55:05       2

Answer

Tried this a couple of way and got it to work.

I first joined 2 tables with individual records for each type, ‘lead’ and ‘follower’ and created a case statement to calculate max start time for each lead and follower start time combination and min end time for each lead and follower end time combination. Stored this in a temp table.

CASE
    WHEN lead_table.start_time > follower_table.start_time THEN lead_table.start_time
    WHEN lead_table.start_time < follower_table.start_time THEN patient_table.start_time_local
    ELSE 0
END as overlap_start_time,
CASE
    WHEN follower_table.end_time < lead_table.end_time THEN follower_table.end_time
    WHEN follower_table.end_time > lead_table.end_time THEN  lead_table.end_time
    ELSE 0
END as overlap_end_time

Then created an outer query to lookup the temp table just created to find the difference between start time and end time for each lead and follower combination in seconds

select temp_table.id,
       temp_table.overlap_start_time,
       temp_table.overlap_end_time,
       DATEDIFF_BIG(second,
            temp_table.overlap_start_time,
            temp_table.overlap_end_time) as overlap_time FROM temp_table