SELECT SYMBOL_IN, TOKEN_IN,

MAX(CASE WHEN CAST(BLOCK_TIMESTAMP AS DATE) = CAST(DATEADD(DAY,-365,GETDATE()) AS DATE) THEN NULLIF(NULLIF(AMOUNT_OUT_USD,0)/NULLIF(AMOUNT_IN,0),0) ELSE NULL END) AS PREVIOUS_PRICE,

MIN(CASE WHEN CAST(BLOCK_TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) THEN NULLIF(NULLIF(AMOUNT_OUT_USD,0)/NULLIF(AMOUNT_IN,0),0) ELSE NULL END) AS CURRENT_PRICE

FROM ethereum.defi.ez_dex_swaps

WHERE CAST(BLOCK_TIMESTAMP AS DATE) = CAST(DATEADD(DAY,-365,GETDATE()) AS DATE)

OR CAST(BLOCK_TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE)

GROUP BY SYMBOL_IN, TOKEN_IN

,

CTE2 AS (

SELECT *,

(CURRENT_PRICE / PREVIOUS_PRICE - 1)*100 AS PERC_CHANGE

FROM CTE

WHERE PREVIOUS_PRICE IS NOT NULL AND CURRENT_PRICE IS NOT NULL

ORDER BY PERC_CHANGE DESC

SELECT AVG(PERC_CHANGE) FROM CTE2

WHERE TOKEN_IN <> '0xcd54df3c19a7ae672897f2a09821d2c287d36326'

SELECT *

FROM ethereum.defi.dim_dex_liquidity_pools