with prices as (

select date_trunc(day,HOUR) ::date date ,

TOKEN_ADDRESS ,

SYMBOL,

avg(price) USD

from ethereum.price.ez_hourly_token_prices

where date_trunc(day,HOUR) ::date >= '2023-01-01'

group by 1,2,3

),

daily as (

select date_trunc(day,BLOCK_TIMESTAMP)::date day ,

TX_HASH ,

ORIGIN_FROM_ADDRESS ,

DESTINATION_CHAIN,

(zeroifnull(AMOUNT) * zeroifnull(USD) ) price_USD

from ethereum.defi.ez_bridge_activity b

join prices p

on (date = date_trunc(week,BLOCK_TIMESTAMP)::date and b.TOKEN_ADDRESS =p.TOKEN_ADDRESS )

where BLOCK_TIMESTAMP >= '2023-01-01'

and EVENT_NAME ilike '%Transfer%'

and TX_HASH not in ('0x1f2b9f44ca381a6c2ca01edaafe4919dd1fedba0b13b5bfbb853b7ca84a0975a','0xe44000ed43e311bb9acbf3f811f066514bef3972fdbb176f5308641a5416ad14','0x3cc0c33249df6ec7ee1122aede9ce17f16694165fe0dde7a110386817087975c','0x31ee8f7428d2ba890a45e8a63cd82cb7cf79d74bb5123b3b0ea29f96a8fe9249','0xaa0137f81c9ac52badbfd41dd38bd81e74e7c687ad40eca0e8fd1b46f468f8e5','0xc1927d65efff0f081de66eac3aaa2bdf90378084e55b73e82667cb0e0571e6bb','0x9874dca6b8b825d4a12c7cda1b7d00f13a0e3c6a59001943328a216605fee308','0xad25f450f481bb5f5d7b7007c187404631a1f2a3ea24d2c86d74b683352b1ce6','0xbe8743633e9446640cb8eece20f8914f836f2f631c85095ce9657853f3d83a7f','0xe7bac983d7c4513d0e89102b9e4552e1ab96b229e9beff293996d68770267d7a')

and DESTINATION_CHAIN is not NULL

select date_trunc(week,day)::date weekly ,

DESTINATION_CHAIN ,

sum (price_USD) bridged_volume ,

sum (bridged_volume) over (partition by DESTINATION_CHAIN order by weekly ) cum_bridged_volume

from daily

group by 1 ,2