SELECT asset_id as token,

AVG(close) AS avg_price,

(AVG(close) - AVG(CASE

WHEN hour BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE() THEN close

ELSE NULL

END)) / AVG(close) AS avg_deviation_weekly_pct

FROM flow.price.fact_prices_ohlc_hourly

WHERE hour BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()

GROUP BY 1 having asset_id is not null order by avg_deviation_weekly_pct desc