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,

CASE

when symbol_out = 'WETH' then AMOUNT_OUT

when SYMBOL_IN = 'WETH' then AMOUNT_IN

end as eth_amount,

CASE

when symbol_out = 'WETH' then SYMBOL_IN

when SYMBOL_IN = 'WETH' then symbol_out

end as NFT_token,

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