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),with_rolling_stats AS (SELECTr.hour,p.price AS btc_price,r.raw_profit,r.raw_loss,-- 24h SMA for profit and lossAVG(r.raw_profit) OVER w24 AS profit_24h_sma,AVG(r.raw_loss) OVER w24 AS loss_24h_sma,AVG(r.raw_profit + ABS(r.raw_loss)) OVER w24 AS realized_volatility_24h_sma,-- Rolling 30-day (720-hour) dynamic cap — adapts to recent regime-- rather than being anchored to all-time spikesAVG(r.raw_profit) OVER w720 + (3 * STDDEV(r.raw_profit) OVER w720) AS profit_cap_rolling,AVG(r.raw_loss) OVER w720 - (3 * STDDEV(r.raw_loss) OVER w720) AS loss_cap_rolling,5000000 AS threshold_line