Query editor
WITH one_day_data AS (WITH relative_strategy as (SELECTnarrative,price_growth,btc_growth,CASE WHEN btc_growth < 0 THEN ((price_growth/btc_growth)*-1)ELSE (price_growth/btc_growth)END AS relative_strategyFROM query_3594576 -- (2)WHERE narrative = 'L1'),mean_of_all_narrative AS (SELECTnarrative, price_growth, APPROX_PERCENTILE(price_growth,0.5) OVER() AS mean_of_all_narrative, price_growth / APPROX_PERCENTILE(price_growth,0.5) OVER() AS relative_strengthFROM query_3594576 -- (2)WHERE narrative = 'L1'), relative_strength AS (SELECTnarrative, a.price_growth, b.relative_strategy, CASE WHEN mean_of_all_narrative = 0 THEN a.price_growth/(mean_of_all_narrative + 1 )WHEN mean_of_all_narrative < 0 THEN (relative_strength *-1 )ELSE (relative_strength)END AS relative_strengthFROM mean_of_all_narrative aLEFT JOIN relative_strategy b using(narrative))SELECTnarrative, price_growth, relative_strategy, relative_strength, (relative_strength + relative_strategy)/2 as optimized_relative_strength, CASE WHEN (relative_strength + relative_strategy)/2 > 1 THEN 'Leading'ELSE 'Lagging'END AS signalFROM relative_strengthORDER BY 4 DESC),seven_days_data AS(withrelative_strategy as (SELECTnarrative, price_growth, btc_growth, CASE WHEN btc_growth < 0 THEN ((price_growth/btc_growth)*-1)ELSE (price_growth/btc_growth)END AS relative_strategyFROM query_3595940WHERE narrative = 'L1'