Query editor
WITH hourly_prices AS (SELECTdate_trunc('hour', minute) AS hour,AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC'AND blockchain IS NULL-- BUFFER: 30 days of chart + 155 days of STH age + cushion = 200AND 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 realized_pnl_usd,SUM( CASE WHEN p_spend.price > p_create.price THEN o.value * (p_spend.price - p_create.price) ELSE 0 END ) AS realized_profit_usd,SUM( CASE WHEN p_spend.price < p_create.price THEN o.value * (p_spend.price - p_create.price) ELSE 0 END ) AS realized_loss_usdFROM 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)-- THE WINDOW: Show exactly the last 30 days on the chartWHERE i.block_time >= NOW() - INTERVAL '30' DAYAND i.block_time > o.block_timeAND (i.block_time - o.block_time) <= INTERVAL '155' DAYGROUP BY 1)SELECTr.hour,p.price AS btc_price,r.realized_profit_usd,r.realized_loss_usd,r.realized_pnl_usd,-- 24-hour SMA for trendAVG(r.realized_pnl_usd) OVER ( ORDER BY r.hour ROWS BETWEEN 23 PRECEDING AND CURRENT ROW ) AS net_pnl_24h_sma,-- 7-day SMA for macro signalAVG(r.realized_pnl_usd) OVER ( ORDER BY r.hour ROWS BETWEEN 167 PRECEDING AND CURRENT ROW ) AS net_pnl_7d_sma,-- Running total of net profit/loss over the 30 daysSUM(r.realized_pnl_usd) OVER ( ORDER BY r.hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_net_pnlFROM sth_realized_hourly rJOIN hourly_prices p ON p.hour = r.hourORDER BY r.hour;