select

block_timestamp,

tx_signer as user,

tx_hash

from near.core.fact_transactions

where 1=1

and TX_STATUS = 'Success'

and block_timestamp >= '2022-09-01'

UNION

select

block_timestamp,

tx_receiver as user,

tx_hash

from near.core.fact_transactions

where 1=1

and TX_STATUS = 'Success'

and block_timestamp >= '2022-09-01'

,

base_table as (

select

user

, date_trunc('month', block_timestamp) as date

, min(date_trunc('month', block_timestamp)) over(partition by user) as earliest_date

, datediff(

'month'

, min(date_trunc('month', block_timestamp)) over(partition by user) -- earliest_date

, date_trunc('month', block_timestamp) -- current date in month

) as difference

from near_users

where 1=1

, count_new_users as(