SELECT

tx_signer as address,

count(distinct tx_hash) as tx_counts,

count(distinct date(block_timestamp)) as days_active,

min(date(block_timestamp)) as first_tx,

max(date(block_timestamp)) as last_tx,

datediff('day', last_tx, getdate()) as days_last_active,

datediff('day', first_tx, getdate()) as age_today

from near.core.fact_transactions

group by 1

),

new_users_stat AS (

SELECT

first_tx,

count(distinct address) as new_users,

count(distinct case when tx_counts >= 5 then address else null end) as five_tx_user,

count(distinct case when days_active >= 7 AND days_last_active <= 7 then address else null end) as active_user,

count(distinct case when days_active = 1 then address else null end) as one_day_user,

count(distinct case when days_active >= 2 then address else null end) as two_or_more_days_user,

count(distinct case when days_active >= 7 then address else null end) as seven_or_more_days_user,

count(distinct case when days_active >= 30 then address else null end) as one_month,

count(distinct case when days_active >= 90 then address else null end) as three_months

FROM wallet_age

GROUP BY 1

)

SELECT

first_tx,

new_users as "New Wallets",

active_user as "New Active Users",

sum(five_tx_user) over (order by first_tx) as "Performed >5 Tx",

sum(new_users) over (order by first_tx) as "Cumulative New Wallets",

sum(active_user) over (order by first_tx) as "Cumulative Active Users",

sum(one_day_user) over (order by first_tx) as "Active Only 1 Day",

sum(two_or_more_days_user) over (order by first_tx) as "Active >= 2 Day",

sum(seven_or_more_days_user) over (order by first_tx) as "Active >= 7 Day",