with chain_id as (select * from (values

(1,'Ethereum'), (56,'BNB'), (42161,'Arbitrum'), (8453,'Base'), (43114,'Avalanche'),(137,'Polygon'),

(10,'Optimism'), (59144,'Linea'), (7565164,'Solana'), (100000001 ,'Neon'), (250,'Fantom')

) as t(chain_id,chain)

, ETH_price as (

select hour::date as price_date,

token_address,

symbol,

decimals,

avg(price) as ETH_price

from ethereum.price.ez_prices_hourly

where symbol = 'WETH'

group by 1,2,3,4

, arbitrum_native as (

select

case when event_name = 'FulfilledOrder' then 'Inflow'

when event_name = 'CreatedOrder' then 'Outflow' end as type,

d.chain as from_chain,

e.chain as to_chain,

case when type = 'Outflow' then b.origin_from_address

when type = 'Inflow' then b.to_address end as user_address,

'ETH' as symbol,

case when type = 'Inflow' then b.amount_usd

when type = 'Outflow' then b.amount_usd - (0.001 * eth_price) end as volume_usd,

case when type = 'Outflow' then (0.001 * eth_price) + (volume_usd * 0.04 * 0.01)

when type = 'Inflow' then 0 end as fee_usd,

'Native Token' as token_address,

a.block_timestamp,

a.tx_hash,

decoded_log:orderId as order_id

from arbitrum.core.fact_decoded_event_logs a

join eth_price p ON (block_timestamp::date = p.price_date)