with contracts as (

select ADDRESS ,SYMBOL

from base.core.dim_contracts

group by 1 ,2

),

prices as (

select HOUR::date date , SYMBOL ,

TOKEN_ADDRESS , DECIMALS,

avg(price) price

from base.price.ez_hourly_token_prices

group by 1 , 2 ,3 ,4 order by 1

select

block_timestamp::date date ,

SYMBOL ,

sum ((RAW_AMOUNT/pow(10,DECIMALS))*price) volume_usd,

sum (volume_usd) over ( order by block_timestamp::date , symbol ) cum_volume_USD

from base.core.fact_token_transfers t join prices p on TOKEN_ADDRESS = CONTRACT_ADDRESS

and p.date =t.BLOCK_TIMESTAMP::date

group by 1 ,2 order by date