Compare prior row using time values

Question

I have this set of data

What I want to do is compare the Start time to the prior row and if the start time falls between the Start and end time of the prior row then flag it. Whether that flag is binary or x doesn't matter, just needs to be counted. So that the new column calls out the instances where the start time of the current row is between the Start and End time of the prior row. My results should look like this.

My thoughts are that LAG and/or LEAD need to be used here but I'm horribly novice at both of those. I'm also thinking I need to create a ROW() for these to make it work. Either way, looking for some guidance on this. I need to be able to track conversation times to see how many times an individual is handling simultaneous conversations (usually no more than 2).

Answer 1

Assuming you have a primary key like ID in the example below you can do something like the below

         WITH data
         AS (SELECT * FROM YOUR_TABLE),
         d1
         AS (SELECT d.*,
                    Lead(start_date)
                      over (
                        ORDER BY id) lead_start_date
             FROM   data d)
    SELECT id,
           start_date,
           end_date,
           CASE
             WHEN lead_start_date BETWEEN start_date AND end_date THEN 1
             ELSE 0
           END marker
    FROM   d1;

Answer 2

One method is exists:

select t.*,
       (case when exists (select 1
                          from t t2
                          where t2.starttime <= t.starttime and
                                t2.endtime >= t.starttime
                         )
             then 1 else 0
        end) as dual_convo
from t;

If I understand correctly, I think you can also use a cumulative maximum:

select t.*,
       (case when max(endtime) over (order by starttime, endtime
                                     rows between unbounded preceding and 1 preceding
                                    ) > starttime
             then 1 else 0
        end) as dual_convo
from t;

Your data only has examples where the previous row overlaps. But presumably you could have overlaps on earlier rows, such as:

1     9
2     3
4     5
8     12

All but the first overlap, and only the first with the "previous" row.