Information that is not at the correct date interval should be retrieved in snowflake-CodePudding

I have a table called 'EMAIL_AUDIT' . In this table , I have Column email_sent_date.

This column contain below detail... I want to retrive the data that is not in 5 days interval in snowflake.

03-05-2021, 08-05-2021, 13-05-2021, 18-05-2021, 23-05-2021, 28-05-2021, 01-06-2021, 06-06-2021, 04-07-2021, 09-07-2021, 14-07-2021, 19-07-2021, 24-07-2021, 29-07-2021, 01-08-2021, 06-08-2021 my data.

i want output like 01-06-2021 and 01-08-2021. Please Help me out.

CodePudding user response：

Using QUALIFY combined with LEAD/LAG:

SELECT *
FROM EMAIL_AUDIT
QUALIFY EMAIL_SENT_DATE < LAG(EMAIL_SENT_DATE,1, EMAIL_SENT_DATE) 
                          OVER(ORDER BY EMAIL_SENT_DATE)   INTERVAL '5 DAYS' 
  AND EMAIL_SENT_DATE > LEAD(EMAIL_SENT_DATE,1, EMAIL_SENT_DATE) 
                        OVER(ORDER BY EMAIL_SENT_DATE) - INTERVAL '5 DAYS';

CodePudding user response：

According to your description, the result should be 01-06-2021 and 04-07-2021, right?

SELECT email_sent_date 
FROM VALUES
('03-05-2021'), ('08-05-2021'), ('13-05-2021'), ('18-05-2021'), ('23-05-2021'), 
('01-06-2021'), ('06-06-2021'), ('04-07-2021'), ('09-07-2021'), ('14-07-2021'), 
('19-07-2021'), ('24-07-2021'), ('29-07-2021'), ('01-08-2021'), ('06-08-2021')
tmp(email_sent_date)
QUALIFY LAG(TO_DATE( email_sent_date, 'DD-MM-YYYY')) OVER (ORDER BY TO_DATE( email_sent_date, 'DD-MM-YYYY'))  
< TO_DATE( email_sent_date, 'DD-MM-YYYY') - INTERVAL '5 DAYS';

 ----------------- 
| EMAIL_SENT_DATE |
 ----------------- 
| 01-06-2021      |
| 04-07-2021      |
 -----------------