SELECT

block_timestamp,

DATE_TRUNC('day', block_timestamp) AS day,

AVG(

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

-- CASE

-- WHEN (amount_out_usd - amount_in_usd) < 0

-- 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 crosschain.defi.ez_dex_swaps

WHERE

(token_in = '0xc5102fe9359fd9a28f877a67e36b0f050d81a3cc'

OR token_out = '0xc5102fe9359fd9a28f877a67e36b0f050d81a3cc')

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

),

hop_slippage AS (

SELECT

day,

-- AVG(avg_slippage) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS svg_slippage_30

avg_slippage

FROM hop_swaps

WHERE rank = 1

SELECT * FROM hop_slippage WHERE day > DATEADD('quarter', -1, CURRENT_DATE()) AND avg_slippage < 5e3 ORDER BY day