customer who have been consecutively paying

Question

I am not sure if this can be done on PostgreSQL, I have a table with customer_id and paid_at (month when they paid) how I can find companies who have been consecutively paying for the last 6 month (since Jan 2018), and of those companies which company has been consecutively paying from the day of their first payment (min(paid_on))?

     customer_id    paid_on
       14535    01/04/2018
       21828    01/10/2017
       52159    01/10/2017
       35033    01/02/2018
       1686     01/08/2016
       7347     01/02/2018
       33721    01/01/2018
       25789    01/07/2017
       62237    01/01/2018
       46184    01/02/2018

Answer 1

Example data:

create table payments(customer_id int, paid_on date);
insert into payments values
    (1, '2018-03-01'),
    (1, '2018-04-01'),
    (1, '2018-06-01'),
    (1, '2018-07-01'),
    (2, '2018-01-01'),
    (2, '2018-04-01'),
    (2, '2018-05-01'),
    (2, '2018-06-01'),
    (2, '2018-07-01'),
    (3, '2018-03-01'),
    (3, '2018-04-01');

The query gives you information on the month of last payment and the number of consecutive paid months in the last series and the number of all paid months:

select 
    customer_id, 
    max(paid_on) as last_payment, 
    count(*) filter (where sum = 0) as consecutive_months,
    count(*) as all_months
from (
    select 
        customer_id, paid_on, 
        sum(grp) over w
    from (
        select 
            customer_id, paid_on, 
            (paid_on <> lag((paid_on- '1 month'::interval)::date, 1, paid_on) over w)::int as grp
        from payments
        window w as (partition by customer_id order by paid_on desc)
        ) s
    window w as (partition by customer_id order by paid_on desc)
    ) s
group by 1

 customer_id | last_payment | consecutive_months | all_months 
-------------+--------------+--------------------+------------
           1 | 2018-07-01   |                  2 |          4
           2 | 2018-07-01   |                  4 |          5
           3 | 2018-04-01   |                  2 |          2
(3 rows)