select

min(block_timestamp) as min_date,

from_address,

to_address

from gnosis.core.fact_transactions

group by 2,3

)

, tb1 as (

select

min_date::date as date,

to_address,

count(distinct from_address) as New_Users_Count,

sum(new_users_count) over (partition by to_address order by date) as Total_Users

from new_user

group by 1,2

)

select

date_trunc('hour', block_timestamp)::date as date,

a.to_address,

new_users_count,

Total_Users,

count(distinct tx_hash) as tx_cnt,

count(distinct from_address) as user_cnt

from gnosis.core.fact_transactions a join tb1 b on a.block_timestamp::date = b.date and a.to_address = b.to_address

where status = 'SUCCESS'

and block_timestamp BETWEEN '2022-10-04' and '2022-10-06'

group by 1,2,3,4

having user_cnt > 100

order by 1