with tb1 as ( select trunc(block_timestamp,'hour') as hourly,

'Ethereum' as chain,

tx_hash,

pool_name,

platform,

CONTRACT_ADDRESS as pool_address,

origin_from_address as trader,

case when token_in = lower('0x85F17Cf997934a597031b2E18a9aB6ebD4B9f6a4') then amount_in

when token_out = lower('0x85F17Cf997934a597031b2E18a9aB6ebD4B9f6a4') then amount_out end as swap_amount

from ethereum.defi.ez_dex_swaps

where (token_in = lower('0x85F17Cf997934a597031b2E18a9aB6ebD4B9f6a4')

or token_out = lower('0x85F17Cf997934a597031b2E18a9aB6ebD4B9f6a4')))

,

tb2 as ( select DISTINCT tx_hash

from ethereum.core.ez_decoded_event_logs

where (event_name ilike '%liquidity%'

or event_name ilike '%mint')

and tx_hash not in (select tx_hash from tb1))

,

tb3 as ( select trunc(a.block_timestamp,'hour') as hourly,

a.tx_hash,

a.origin_from_address as provider,

to_address as pool_address,

'ETH' AS SYMBOL,

a.amount as liquidity_amount,

from ethereum.core.ez_native_transfers a join tb1 b on a.to_address = b.pool_address

where a.tx_hash not in (select tx_hash from tb1)

select trunc(a.block_timestamp,'hour') as hourly,

a.tx_hash,

a.origin_from_address as provider,

to_address as pool_address,

SYMBOL,