Query editor
WITH hourly_prices AS (SELECTdate_trunc('hour', minute) AS hour,AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC'AND blockchain IS NULLAND minute >= NOW() - INTERVAL '200' DAYGROUP BY 1),sth_realized_hourly AS (SELECTdate_trunc('hour', i.block_time) AS hour,SUM(o.value * (p_spend.price - p_create.price)) AS raw_pnl,SUM(CASE WHEN p_spend.price > p_create.priceTHEN o.value * (p_spend.price - p_create.price)ELSE 0 END) AS raw_profit,SUM(CASE WHEN p_spend.price < p_create.priceTHEN o.value * (p_spend.price - p_create.price)ELSE 0 END) AS raw_lossFROM bitcoin.inputs iJOIN bitcoin.outputs oON o.tx_id = i.spent_tx_idAND o.index = i.spent_output_numberAND o.block_time >= NOW() - INTERVAL '200' DAYJOIN hourly_prices p_spendON p_spend.hour = date_trunc('hour', i.block_time)JOIN hourly_prices p_createON p_create.hour = date_trunc('hour', o.block_time)WHEREi.block_time >= NOW() - INTERVAL '30' DAYAND i.block_time > o.block_timeAND (i.block_time - o.block_time) <= INTERVAL '155' DAYGROUP BY 1),stats AS (SELECTAVG(raw_profit) + (3 * STDDEV(raw_profit)) AS profit_cap,AVG(raw_loss) - (3 * STDDEV(raw_loss)) AS loss_capFROM sth_realized_hourly),with_sma AS (SELECTr.hour,p.price AS btc_price,s.profit_cap,s.loss_cap,LEAST(AVG(r.raw_profit) OVER (ORDER BY r.hour ROWS BETWEEN 23 PRECEDING AND CURRENT ROW),s.profit_cap) AS profit_24h_sma,