How To: Search for overlapping time windows

This logic is particularly useful when looking for all the events that overlap a specific time window.   For example:  You want to know all the TV watch sessions in your DVRs that you have distributed across the world which overlap a range of time.

Here is a Snowflake SQL example of performing this operation complete with test cases as proof it works.

/* This proof tests the logic for Overlapping window filters */
SET FILTER_START = '2020-02-01 00:00:00 +0000';
SET FILTER_END = '2020-02-20 23:59:59 +0000';

CREATE OR REPLACE TEMPORARY TABLE TEST (TEST_TYPE STRING, START_TIMESTAMP TIMESTAMP_NTZ, END_TIMESTAMP TIMESTAMP_NTZ);

INSERT INTO TEST
SELECT COLUMN1 AS TEST_TYPE, COLUMN2::TIMESTAMP_NTZ AS START_TIMESTAMP, COLUMN3::TIMESTAMP_NTZ AS END_TIMESTAMP
 FROM (VALUES('OUTSIDE LEFT', '2020-01-01 00:00:00 +0000', '2020-01-02 00:00:00 +0000'),  
             ('OUTSIDE RIGHT', '2020-02-24 00:00:00 +0000', '2020-02-25 00:00:00 +0000'),  
             ('STRADDLE LEFT', '2020-01-15 00:00:00 +0000', '2020-02-02 00:00:00 +0000'),  
             ('STRADDLE RIGHT', '2020-02-10 00:00:00 +0000', '2020-02-25 00:00:00 +0000'),  
             ('INSIDE', '2020-02-10 00:00:00 +0000', '2020-02-11 00:00:00 +0000'),  
             ('STRADDLE BOTH SIDES', '2020-01-01 00:00:00 +0000', '2020-03-25 00:00:00 +0000')
      );
      
SELECT *
  FROM TEST
 WHERE (END_TIMESTAMP > $FILTER_START AND START_TIMESTAMP < COALESCE($FILTER_END,DATEADD(DAY, 1, $FILTER_START)));