Query editor
-- with-- mean_of_all_narrative AS (-- SELECT-- symbol as coin-- , oneday_growth as price_growth-- , APPROX_PERCENTILE(oneday_growth,0.5) OVER() AS mean_of_all_narrative-- , oneday_growth / APPROX_PERCENTILE(oneday_growth,0.5) OVER() AS relative_strength-- FROM query_3593395 -- L1 ~ 1D price growth-- )-- , relative_strength AS (-- SELECT-- coin-- , price_growth-- , mean_of_all_narrative-- , CASE WHEN mean_of_all_narrative = 0 THEN price_growth/(mean_of_all_narrative + 1 )-- WHEN mean_of_all_narrative < 0 THEN (relative_strength *-1 )-- ELSE (relative_strength)-- END AS relative_strength-- FROM mean_of_all_narrative-- )-- SELECT-- coin-- , price_growth-- , mean_of_all_narrative-- , relative_strength-- , CASE WHEN relative_strength > 1 THEN 'Leading'-- ELSE 'Lagging'-- END AS leading_lagging-- FROM relative_strength-- ORDER BY 4 DESC/* Because we added new metric, so we build new query*/withbtc as (with today as (SELECTdate_trunc('day', minute) as day, symbol, AVG(price) as price_today-- , price as price_todayFROM prices.usd_latestWHERE blockchain is NULLAND symbol = 'BTC'-- AND DATE(minute) = DATE(NOW())-- AND date_trunc('day', minute) = NOW()GROUP BY 1,2), one_day as (SELECTdate_trunc('day', minute) as day, symbol, AVG(price) as price_24hFROM prices.usdWHERE blockchain is NULLAND symbol = 'BTC'AND DATE(minute) = DATE(NOW() - interval '24' hour)AND EXTRACT(HOUR FROM minute) >= 0 AND EXTRACT(HOUR FROM minute) < 1GROUP BY 1,2)