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.price THEN o.value * (p_spend.price - p_create.price) ELSE 0 END ) AS raw_profit,SUM( CASE WHEN p_spend.price < p_create.price THEN o.value * (p_spend.price - p_create.price) ELSE 0 END ) AS raw_lossFROM bitcoin.inputs iJOIN bitcoin.outputs o ON o.tx_id = i.spent_tx_idAND o.index = i.spent_output_numberAND o.block_time >= NOW() - INTERVAL '200' DAYJOIN hourly_prices p_spend ON p_spend.hour = date_trunc('hour', i.block_time)JOIN hourly_prices p_create ON p_create.hour = date_trunc('hour', o.block_time)WHERE i.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 (-- Calculate the "Cap" (Mean + 3 Std Devs) for the whole 30-day periodSELECTAVG(raw_profit) + (3 * STDDEV(raw_profit)) AS profit_cap,AVG(raw_loss) - (3 * STDDEV(raw_loss)) AS loss_capFROM sth_realized_hourly)SELECTr.hour,p.price AS btc_price,-- Cap the profit at 3 Std Devs if it's an outlierLEAST(AVG(r.raw_profit) OVER (ORDER BY r.hour ROWS BETWEEN 23 PRECEDING AND CURRENT ROW), s.profit_cap) AS profit_24h_sma,-- Cap the loss at 3 Std Devs if it's an outlierGREATEST(AVG(r.raw_loss) OVER (ORDER BY r.hour ROWS BETWEEN 23 PRECEDING AND CURRENT ROW), s.loss_cap) AS loss_24h_sma,-- Total Activity (now based on capped values)LEAST(AVG(r.raw_profit + ABS(r.raw_loss)) OVER (ORDER BY r.hour ROWS BETWEEN 23 PRECEDING AND CURRENT ROW), s.profit_cap + ABS(s.loss_cap)) AS realized_volatility_24h_sma,5000000 AS threshold_lineFROM sth_realized_hourly rJOIN hourly_prices p ON p.hour = r.hourCROSS JOIN stats sORDER BY r.hour;