Query editor
with MSTR as(select * from (select DATE_PARSE(quotedate, '%m/%d/%Y') as Days, close as COBfrom dune.agarciaut.dataset_mstr_prices_upload) Awhere Days>=date_trunc('hour',now()-interval '365' day)),BTC as (selectavg(price) as BTC_Price,date_trunc('day',minute) as Daysfrom prices.usdwhere contract_address is null and symbol='BTC'and minute>=date_trunc('hour',now()-interval '365' day)group by date_trunc('day',minute)order by date_trunc('day',minute)),Combine as (select a.*, b.BTC_Pricefrom MSTR a left join BTC bon a.Days = b.Daysorder by days),Delta as (select Days, COB as MSTR_Price, BTC_Price,(COB - lag(COB,1) over (order by Days))/ lag(COB,1) over (order by Days) as Delta_MSTR,(BTC_Price - lag(BTC_Price,1) over (order by Days))/ lag(BTC_Price,1) over (order by Days) as Delta_BTCfrom combinegroup by 1,2,3order by 1)select Corr(Delta_MSTR,Delta_BTC) as Correlationfrom Delta