Return first value after each 60 day period from an Exasol table

48 Views Asked by At

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.

0

There are 0 best solutions below