Window function in Snowflake

Question

My data is structured as below -

1.For each ID month denotes reporting month, Sub created is the original subscription purchase date, status = whether customer was active or not, tenure is lifetime months ( It resets to 1 upon the customer returning )

ID  Month       Sub_created status  tenure
100 2017-02-01  2017-02-01  active  1
100 2017-03-01              active  2
100 2017-04-01              active  3
100 2017-05-01              churned 3
100 2021-02-01  2021-02-01  active  1
100 2021-03-01            active    2
100 2021-04-01            active    3
100 2021-05-01            active    4
100 2021-06-01            active    5
100 2021-07-01            active    6

I want to be able to have sub created for all the rows till it has a new subscription date. The output I am trying to get is below -

ID  Month       Sub_created status  tenure
100 2017-02-01  2017-02-01  active  1
100 2017-03-01  2017-02-01  active  2
100 2017-04-01  2017-02-01  active  3
100 2017-05-01  2017-02-01  churned 3
100 2021-02-01  2021-02-01  active  1
100 2021-03-01  2021-02-01  active  2
100 2021-04-01  2021-02-01  active  3
100 2021-05-01  2021-02-01  active  4
100 2021-06-01  2021-02-01  active  5
100 2021-07-01  2021-02-01  active  6

Can anyone suggest snowflake code ? Thanks

Maja F. Maja F. · Accepted Answer · 2021-07-27T19:56:40

You can use the last_value() window function like so:

with CTE as (
select 100 as ID, '2017-02-01' as Month, '2017-02-01' as Sub_created, 'active' as status, 1 as tenure union all 
select 100 as ID, '2017-03-01' as Month, null         as Sub_created, 'active' as status, 2 as tenure union all 
select 100 as ID, '2017-04-01' as Month, null         as Sub_created, 'active' as status, 3 as tenure union all 
select 100 as ID, '2017-05-01' as Month, null         as Sub_created, 'churned' as status, 3 as tenure union all 
select 100 as ID, '2021-02-01' as Month, '2021-02-01' as Sub_created, 'active' as status, 1 as tenure union all 
select 100 as ID, '2021-03-01' as Month, null         as Sub_created, 'active' as status, 2 as tenure union all 
select 100 as ID, '2021-04-01' as Month, null         as Sub_created, 'active' as status, 3 as tenure union all 
select 100 as ID, '2021-05-01' as Month, null         as Sub_created, 'active' as status, 4 as tenure union all 
select 100 as ID, '2021-06-01' as Month, null         as Sub_created, 'active' as status, 5 as tenure union all 
select 100 as ID, '2021-07-01' as Month, null         as Sub_created, 'active' as status, 6 as tenure
)
select ID, Month, Sub_created as Sub_created_orig, 
last_value(sub_created ignore nulls) over (partition by id order by month rows between unbounded preceding and current row) as Sub_created_new,
status, tenure
from CTE
order by ID, month;

Window function in Snowflake

1 Answers