(

select avg(PRICE) as price_value , to_date(HOUR) as price_date from optimism.core.fact_hourly_token_prices

where TOKEN_ADDRESS='0x4200000000000000000000000000000000000042'

group by 2

)

, optimism_transactions as

(

select count(*) as number_of_transactions , count(distinct FROM_ADDRESS) as transactions_users , to_date(BLOCK_TIMESTAMP) as tran_date

from optimism.core.fact_transactions

group by tran_date

)

, optimism_transfer as

(

select count(*) as number_of_token_transfer , count(distinct FROM_ADDRESS) as transfers_users , to_date(BLOCK_TIMESTAMP) as tran_date

from optimism.core.fact_token_transfers

where CONTRACT_ADDRESS='0x4200000000000000000000000000000000000042'

group by tran_date

)

select price_value,transactions_users,price_date from op_price left join optimism_transactions

on price_date=tran_date