CONTRACT ,

min(date_trunc('month',TIMESTAMP)) over (partition by CONTRACT) as signup_date,

date_trunc('month', TIMESTAMP) as activity_date ,

datediff('month',signup_date,activity_date) as difference

from external.tokenflow_starknet.decoded_transactions

),

unp as (

select

TO_VARCHAR(signup_date, 'yyyy-MM') as cohort_date,

difference as months,

count (distinct CONTRACT) as users

from

base

where

datediff('month', signup_date, current_date()) <= 12

group by

1,2

order by

1

),

fine as (

select

u.*,

p.USERS as user0

from

unp u

left join unp p on u.COHORT_DATE = p.COHORT_DATE

where

p.MONTHS = 0

COHORT_DATE,

MONTHS,