PSQL: find invoice that was created in less than 8 months of another invoice

Question

Table: Invoice
user_id
created_at

The goal is to find the invoice, that was created in less than 8 months of another invoice, both belonging to the same user.

Exemple:

Invoice 2: created_at: 2020-12-01 user_id: 1
Invoice 1: created_at: 2020-08-01 user_id: 1

Invoice 4: created_at: 2020-12-01 user_id: 2
Invoice 3: created_at: 2019-12-01 user_id: 2

Invoice 7: created_at: 2019-09-01 user_id: 3
Invoice 6: created_at: 2019-07-01 user_id: 3
Invoice 5: created_at: 2019-06-01 user_id: 3

Query should return Invoice 2, because another one (Invoice 1) has been created in less that 8 months before Invoice 1.

Query should also return Invoice 7, because another one (Invoice 6) has been created in less that 8 months before Invoice 7.

Query should also return Invoice 6, because another one (Invoice 5) has been created in less that 8 months before Invoice 6.

So far:

Invoice.where(created_at: 8.months.ago..Time.now).group_by(&:user_id).select { |k, v| v.size > 1 }

select * from invoices where created_at BETWEEN ('2020-04-21 14:05:57.009849' AND '2020-12-21 14:05:57.009949')

The problem with my approach is that it find only for last 8 months, not in general. And it uses ruby+sql, SQL only can be better.

Answer 1

This should work:

WITH cte AS (
    SELECT  user_id
        ,   invoice
        ,   created_at
        ,   LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) previous
    FROM    invoice
)
SELECT  *
FROM    cte
WHERE   AGE(created_at, previous) <= INTERVAL '8 months';