CODEWITHSUNDEEP
sqlpostgresql
LanceS
LanceS

Reputation: 87

How to assign rows to different cohorts based on aggregation criteria

I have a list of transactions in a postgresql table and I need to bucket them into groups based on when they took place, and whether the running total of transactions has surpassed a threshold.

A "Cohort" here is defined by the last day of the month and whether a threshold of $100 has been met.

Example: A "Cohort" becomes a "Cohort" on the last day of the month when a batch of transactions is >= $100

SAMPLE DATA:

|TRANS_DATE|AMOUNT|

2018-01-01 | $10
2018-01-15 | $10
2018-01-30 | $50
2018-02-27 | $80
2018-03-05 | $101
2018-04-05 | $1
2018-05-15 | $80
2018-06-05 | $1
2018-07-26 | $18

Given this data, I would expect the results of an aggregate query to be:

DATE | AMOUNT | COHORT

2018-02-28 | $150 | 1
2018-03-31 | $101 | 2
2018-07-31 | $100 | 3

I keep thinking I would need some type of loop for this problem, which I don't believe is possible.

I've been trying stuff similar to:

with st as 
(
select distinct(date_trunc('month', "date") + interval '1 month' - interval '1 day') as date,
sum(amount) over (order by date_trunc('month', date) + interval '1 month' - interval '1 day') as total
from a1
order by 1  
)
select st.*
, case when lag(total) over (order by date) <= 100 then 1 end as cohort1 
, floor(total/100)
from st

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is quite complicated. I'm pretty sure you need recursive CTEs -- because you are hitting a boundary and then starting over.

Try this:

with tt as (
      select date_trunc('mon', trans_date) as mon, sum(amount) as amount,
             lead(sum(amount)) over (order by min(trans_date)) as next_amount,
             row_number() over (order by min(trans_date)) as seqnum
      from t
      group by 1
     ),
     cte as (
      select mon, amount, seqnum, 1 as cohort, (amount >= 100) as is_new_cohort
      from tt
      where seqnum = 1
      union all
      select tt.mon,
             (case when is_new_cohort then tt.amount else cte.amount + tt.amount end) as amount,  
             tt.seqnum,
             (case when is_new_cohort then cohort + 1 else cohort end) as cohort,
             ( (case when is_new_cohort then tt.amount else cte.amount + tt.amount end) >= 100) as is_new_cohort
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select cohort, max(amount), max(cte.mon + interval '1 month' - interval '1 day') as mon
from cte
group by 1
order by 1;

Here is a db<>fiddle.

Upvotes: 1

Related Questions