select

tx_signer,

min(date_trunc('day', block_timestamp)) reg_day

from near.core.fact_transactions

where tx_receiver = 'aurora'

and tx_status = 'Success'

group by tx_signer

),

new_users_per_day as(

select

reg_day day,

count(distinct tx_signer) new_users,

sum(new_users) over(order by day) cum_new_users

from new_user_reg_dates

group by day

),

unique_users_per_day as(

select

date_trunc('day', block_timestamp) day,

count(distinct tx_signer) unique_users

from near.core.fact_transactions

where day >= current_date - 365 and tx_receiver = 'aurora'

and tx_status = 'Success'

--and tx_signer not like '%.aurora'

group by day

order by day

),

tx_per_day as(

select

date_trunc('day', block_timestamp) day,

count(distinct tx_hash) tx_count,

sum(tx_count) over(order by day) cum_tx

from near.core.fact_transactions

where tx_receiver = 'aurora'

and tx_status = 'Success'