t1 as (

SELECT

date_trunc('hour',block_timestamp) as hours,

count(distinct tx_hash) as transactions,

count(distinct from_address) as users,

transactions/users as transactions_per_user

from gnosis.core.fact_transactions where hours between '2022-10-02' and '2022-10-08'

group by hours

),

t2 as (

SELECT

distinct from_address,min(block_timestamp) as first_transaction

from gnosis.core.fact_transactions group by from_address

),

t3 as (

SELECT

date_trunc('hour',first_transaction) as hours,

count(distinct from_address) as new_users

from t2 where hours between '2022-10-02' and '2022-10-08'

group by hours

)

SELECT t1.hours, transactions,users, transactions_per_user,new_users

from t1,t3 where t1.hours=t3.hours