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 ,

(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')

),

bridgers as (

select ORIGIN_FROM_ADDRESS bridger,

sum (price_USD) bridged_volume ,

count (DISTINCT TX_HASH) num_bridges

from daily

group by 1

select case when bridged_volume < 10 then 'Less than $10'

when bridged_volume BETWEEN 10 and 50 then '$10 - $50'

when bridged_volume BETWEEN 50 and 100 then '$50 - $100'

when bridged_volume BETWEEN 100 and 250 then '$100 - $250'

when bridged_volume BETWEEN 250 and 500 then '$250 - $500'

when bridged_volume BETWEEN 500 and 1000 then '$500 - $1K'

when bridged_volume BETWEEN 1000 and 5000 then '$1K - $5K'

when bridged_volume BETWEEN 5000 and 10000 then '$5K - $10K'

else 'More than $10K'

end range ,