Below you will find a table with dummy data. Can anyone help me write a query which returns the touchpoint_id for the very first touchpoint and then always the first touchpoint after a 60 day period?
With the data below, the query should return 00112023-01-03, as this was the first touchpoint, and then 00112023-04-20, since 2023-04-20 is the first touchpoint date after a 60 day period. Then 00112023-06-23, since this is the first touchpoint after a 60 day period from the previous touchpoint (2023-04-20), and so on.
I've been having issues writing a recursive query in Exasol which returns the expected results.
CREATE OR REPLACE table1 (
touchpoint_id VARCHAR(30),
touchpoint_date DATE
);
INSERT INTO table1 (touchpoint_id, touchpoint_date)
VALUES
('00112023-01-03',TO_DATE('2023-01-03','YYYY-MM-DD')),
('00112023-02-05',TO_DATE('2023-02-05','YYYY-MM-DD')),
('00112023-03-02',TO_DATE('2023-03-02','YYYY-MM-DD')),
('00112023-04-20',TO_DATE('2023-04-20','YYYY-MM-DD')),
('00112023-06-23',TO_DATE('2023-06-23','YYYY-MM-DD')),
('00112023-07-05',TO_DATE('2023-07-05','YYYY-MM-DD')),
('00112023-07-26',TO_DATE('2023-07-26','YYYY-MM-DD')),
('00112023-07-26',TO_DATE('2023-07-26','YYYY-MM-DD'));
I tried to achieve this with the lead() and lag() window functions, but couldn't identify the first touchpoint as a starting point for the next 60 day period. I tried to do this with the CONNECT BY operator, but wasn't sure about the syntax for this use case.