Partition rows where dates are between the previous dates

Question

I have the below table.

I want to identify overlapping intervals of start_date and end_date.

*edit I would like to remove the row that has the least amount of days between the start and end date where those rows overlap.

Example:

pgid 1 & pgid 2 have overlapping days. Remove the row that has the least amount of days between start_date and end_date.

Table A

id   pgid  Start_date    End_date      Days
 1    1    8/4/2018      9/10/2018      37
 1    2    9/8/2018      9/8/2018        0
 1    3    10/29/2018    11/30/2018     32
 1    4    12/1/2018     sysdate        123    

Expected Results:

 id   Start_date    End_date     Days
 1     8/4/2018      9/10/2018    37 
 1     10/29/2018    11/30/2018   32  
 1     12/1/2018     sysdate      123    

Answer 1

Maybe lead and lag:

SELECT 
CASE 
WHEN END_DATE > LEAD (START_DATE) OVER (PARTITION BY id ORDER BY START_DATE) THEN 1
WHEN START_DATE < LAG (END_DATE) OVER (PARTITION BY id ORDER BY START_DATE) THEN 1 
ELSE 0
END OVERLAP_FLAG
FROM A