SELECT

avg(PRICE) as pr,

date_trunc('day', HOUR) as day

FROM

crosschain.price.ez_hourly_token_prices

WHERE

SYMBOL = 'WETH'

and date_trunc('day', hour) >= '2023-10-13'

GROUP by

2

),

flooring as (

SELECT

block_timestamp :: date as date,

tx_hash,

ORIGIN_FROM_ADDRESS as ua,

CASE

when symbol_out = 'WETH'

and AMOUNT_OUT_usd is not null then AMOUNT_OUT_usd

when SYMBOL_IN = 'WETH'

and AMOUNT_IN_usd is not null then AMOUNT_IN_usd

when symbol_out = 'WETH'

and AMOUNT_OUT_usd is null then AMOUNT_OUT * pr

when SYMBOL_IN = 'WETH'

and AMOUNT_in_usd is null then AMOUNT_in * pr

end as USD_amount

FROM

ethereum.defi.ez_dex_swaps s

left join price p on p.day = s.block_timestamp :: date

WHERE

TOKEN_IN in (

SELECT

DECODED_LOG:floorToken

from

ethereum.core.ez_decoded_event_logs