WITH eul_swaps AS (

SELECT

block_timestamp,

DATE_TRUNC('day', block_timestamp) AS day,

AVG(

CASE

WHEN (amount_out_usd - amount_in_usd) < 0

THEN DIV0(ABS((amount_out_usd - amount_in_usd)), amount_in_usd) * 100

ELSE 0

END

) OVER (PARTITION BY day ORDER BY block_timestamp) AS avg_slippage,

ROW_NUMBER() OVER (PARTITION BY day ORDER BY block_timestamp DESC) AS rank

FROM ethereum.defi.ez_dex_swaps

WHERE

(token_in = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b'

OR token_out = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b')

AND block_timestamp > DATE('2021-12-31 00:00')

),

eul_volume_trade_count_ats AS (

SELECT

day,

avg_slippage

FROM eul_swaps

WHERE rank = 1

SELECT * FROM eul_volume_trade_count_ats ORDER BY day