Select rows based on desired date difference from first row by group – SQL

April 10, 2024

I can generate a table with the following structure – in actuality, this is more than 50,000 rows long – but below example is a simplified representation:

Row    Account Number     Date
1      1001               2011-01-10
2      1001               2011-02-01
3      1001               2011-02-20
4      1001               2011-02-22
5      2001               2011-04-11
6      2001               2012-01-01
7      2001               2012-01-30
8      2001               2012-02-09

I would like to select the first row and next row for each account number where the date difference is at least 30-days.

So in the example above, I would like to select Rows #1 and #3 for Account Number 1001, Rows #5 and #6 for Account Number 2001.

How to generate this (in snowflake SQL)?

>Solution :

I’m trying to imagine a way of pulling this that doesn’t have an interim result set with a slight cross-join involved, but I’m not sure it’s possible. One option:

SELECT t1.row, t1.accountnumber, t1.date as firstdate, t2.row as 30dayplusrow, t2.date as 30dayplusdate
FROM yourtable t1 
    INNER JOIN yourtable t2
       ON t1.accountnumber = t2.accountnumber
       AND DATEADD(day, 2, t1.date) >= t2.date 
QUALIFY ROW_NUMBER() OVER (PARTITION BY accountnumber ORDER BY firstdate, 30dayplusdate) = 1