date_trunc('day',HOUR) as day,

TOKEN_ADDRESS,

SYMBOL,

avg(PRICE) as price

from gnosis.price.ez_prices_hourly

group by 1,2,3

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,

amount_in,

amount_out,

amount_in * p1.price as in_amount_usd,

amount_out * p2.price as out_amount_usd,

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

from ethereum.defi.ez_dex_swaps s

left join price_tab p1 on s.BLOCK_TIMESTAMP::date = p1.day and s.symbol_in = p1.SYMBOL

left join price_tab p2 on s.BLOCK_TIMESTAMP::date = p2.day and s.symbol_out = p2.SYMBOL

where (SYMBOL_IN = 'OLAS'

or SYMBOL_OUT = 'OLAS')

AND CONTRACT_ADDRESS = '0x09d1d767edf8fa23a64c51fa559e0688e526812f'

and BLOCK_TIMESTAMP>='2024-10-01'

),

eth_swaps as (

select

BLOCK_TIMESTAMP::date as day,

'Ethereum' as chain,