((select * from

arbitrum.core.fact_token_transfers a

inner join

arbitrum.core.dim_labels b

on a.TO_ADDRESS=b.address)

union all

(select * from

arbitrum.core.fact_token_transfers a

inner join

arbitrum.core.dim_labels b

on a.from_ADDRESS=b.address)

, t2 as

(select SYMBOL,NAME, RAW_AMOUNT/pow(10,DECIMALS) as volume,

date_trunc(day,BLOCK_TIMESTAMP) as date,

ADDRESS_NAME,LABEL_TYPE,LABEL_SUBTYPE,project_name

from t1 a

inner join arbitrum.core.dim_contracts b

on a.CONTRACT_ADDRESS=b.address),

price as

(select

date_trunc('day',HOUR) as datee,

avg(PRICE) as price, SYMBOL

from

ethereum.core.fact_hourly_token_prices

group by 1,3)

select sum(VOLUME*PRICE) as USD_volume,project_name,date

from t2 a inner join

price b on a.SYMBOL=b.SYMBOL and a.date=b.datee