select version();

CREATE TABLE test ( id INT NOT NULL, dc TIMESTAMPTZ NOT NULL, cs TEXT NOT NULL, ce TEXT NOT NULL, UNIQUE (id, dc) );

INSERT INTO test VALUES (1, '2020-05-27 16:33:52.000', 'green', 'yellow'), (1, '2020-06-11 20:12:18.000', 'yellow', 'red'), (1, '2020-06-11 20:20:58.000', 'red', 'green'), (2, '2020-08-06 14:59:21.000', 'green', 'yellow'), (2, '2021-03-03 14:31:44.000', 'yellow', 'red'), (3, '2021-04-28 12:36:45.000', 'green', 'red');

-- SELECT * FROM test; -- check

SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), cs, ce FROM test;

SELECT id, dc AS date_from, TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'), cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'), ce FROM test;

SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'), cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'), ce FROM test;

SELECT id, dummy, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, dummy, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb;

SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb;

-- -- records where the end of the range falls anywhere >= 2021:01:01 00:00:00 -- SELECT * FROM ( SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb ) AS t3 WHERE trb && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');

-- -- Status counts at exactly New Year, 2021 -- SELECT cs, COUNT(cs), TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') AS r FROM ( SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb ) AS t3 WHERE trb && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') GROUP BY cs, r;