from near.core.fact_transactions

group by 2)

,

new_user as ( select DISTINCT tx_signer

from users

where date >= '2023-01-01'

)

,

final as ( select a.tx_signer, count(DISTINCT(block_timestamp::date)) as active_days

from near.core.fact_transactions a join new_user b on a.tx_signer = b.tx_signer

where block_timestamp::date >= '2023-01-01'

and block_timestamp::date != '2023-01-10'

group by 1)

,

breakdown as ( select tx_signer,

case when active_days = 1 then '1 Day'

when active_days <= 7 then '1 Week'

when active_days <= 14 then '2 Weeks'

when active_days <= 30 then '1 Month'

when active_days <= 60 then '2 Months'

when active_days > 60 then '+2 Months' end as category

from final

select category, count(DISTINCT(tx_hash)) as total_tx, sum(deposit/pow(10,24)) as total_near,

count(DISTINCT(a.tx_signer)) as total_user

from near.core.fact_transfers a join breakdown b on a.tx_signer = b.tx_signer

where block_timestamp >= '2023-01-01'

group by 1