PostgreSQL window function: partition with condition

Question

I'm trying to create a query with fields:

data - Date sequence
user_id - User ID
is_paid - Did the user pay this month
number_of_not_paid - the number of months since the last payment, if the payment is in this month, then 0

select
   date,
   user_id,
   is_paid,
   (case when is_paid
       then 0
       else sum(case when is_paid then 0 else 1 end) over (partition by user_id order by date)
   end)
from data

The result I get is:

date	user_id	is_paid	num
2020-01-01	1	true	0
2020-02-01	1	false	1
2020-03-01	1	false	2
2020-04-01	1	true	0
2020-05-01	1	false	3
2020-06-01	1	true	0

And the result I would like to get is:

date	user_id	is_paid	num
2020-01-01	1	true	0
2020-02-01	1	false	1
2020-03-01	1	false	2
2020-04-01	1	true	0
2020-05-01	1	false	1
2020-06-01	1	true	0

How I can fix my query for correct result?

Explain little bit more what does this imply ? You want to sum only when is_paid = false and when it is in a row ? When it is break by a true then you want to start from 1 again ? — VBoka
– VBoka, Commented May 25, 2022 at 9:58
i think you'll need recursive for this. check is_paid and if it is false add 1 to the previous value in the same field, else put 0 — samkart
– samkart, Commented May 25, 2022 at 9:58
here's an example of how recursive can help you -- DB Fiddle — samkart
– samkart, Commented May 25, 2022 at 10:36

Mike Organek · Accepted Answer · 2022-05-25 10:38:55Z

2

You want to reset the delinquency timer every time the user pays, so first mark each time the user pays (boolean can be cast to int to be summed):

with runs as (
  select date, user_id, is_paid, 
         sum(is_paid::int) over (partition by user_id
                                     order by date) as run_number
    from my_table
)

With these runs marked, you can then sum the preceding false values within the (user_id, run_number) window:

select date, user_id, is_paid,
       sum((not is_paid)::int) over (partition by user_id, run_number
                                         order by date) as num
  from runs;

 date       | user_id | is_paid | num
 :--------- | ------: | :------ | --:
 2020-01-01 |       1 | t       |   0
 2020-02-01 |       1 | f       |   1
 2020-03-01 |       1 | f       |   2
 2020-04-01 |       1 | t       |   0
 2020-05-01 |       1 | f       |   1
 2020-06-01 |       1 | t       |   0

db<>fiddle here

answered May 25, 2022 at 10:38

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NPreston Over a year ago

I think it's a bit complicated. Could I use rows between ... and ... for my query?

Mike Organek Over a year ago

@NPreston If you can figure out a way to apply rows, groups, or ranges to achieve this result, then please post your solution as I would like to learn how that is done.

Collectives™ on Stack Overflow

PostgreSQL window function: partition with condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related