with price_tab as (

HOUR,

TOKEN_ADDRESS,

SYMBOL,

avg(PRICE) as price

from arbitrum.price.ez_prices_hourly

where HOUR >= '2024-07-30'

and SYMBOL in ('LAVA','WETH')

group by 1,2,3

),

base as (SELECT BLOCK_TIMESTAMP,

IFF(amount_in_usd is not null OR amount_out_usd is not null, 'Good', 'Miss') as checker,

amount_in_usd,

amount_out_usd,

symbol_in,

symbol_out,

tx_hash,

ORIGIN_FROM_ADDRESS as swapper,

amount_in,

amount_out,

amount_in * p1.price as in_amount_usd,

amount_out * p2.price as out_amount_usd,

case

when checker = 'Miss' and symbol_in = 'WETH' then amount_in * p1.price

when checker = 'Miss' and symbol_out = 'WETH' then amount_out * p2.price

else amount_out_usd end as amount_usd

-- COALESCE(amount_out_usd, amount_in_usd, out_amount_usd, in_amount_usd) as amount_usd

from arbitrum.defi.ez_dex_swaps s

left join price_tab p1 on date_trunc('hour',s.BLOCK_TIMESTAMP) = p1.HOUR and s.symbol_in = p1.SYMBOL

left join price_tab p2 on date_trunc('hour',s.BLOCK_TIMESTAMP) = p2.HOUR and s.symbol_out = p2.SYMBOL

where (SYMBOL_IN = 'LAVA'

or SYMBOL_OUT = 'LAVA')