Query editor
-- NVT Signal — bitcoin.inputs JOIN bitcoin.outputs (SOPR ile aynı yöntem)-- bitcoin.outputs.value ve bitcoin.transactions.output_value birimleri belirsizdir.-- bitcoin.inputs JOIN bitcoin.outputs: SOPR'da doğrulandı, güvenilir satoshi değerleri döner.-- 90-gün pencere (community plan hız limiti)WITHprice_daily AS (SELECT DATE_TRUNC('day', minute) AS day, AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC' AND blockchain IS NULLAND minute >= NOW() - INTERVAL '500' dayGROUP BY 1),-- Harcanan çıktıların değeri (input = gerçek ekonomik aktivite, change değil)-- NOT: bitcoin.outputs.value birimi belirsiz; /1e8 YOK (test: BTC cinsinden olduğu hipotezi)tx_vol AS (SELECTDATE_TRUNC('day', i.block_time) AS day,SUM(CAST(o.value AS DOUBLE)) AS btc_volumeFROM bitcoin.inputs iJOIN bitcoin.outputs oON i.spent_tx_id = o.tx_idAND i.spent_output_number = o.indexWHERE i.block_time >= NOW() - INTERVAL '90' dayAND o.value > 0GROUP BY 1),supply AS (SELECT 19870000.0 AS btc)SELECTp.day AS date,p.price,ROUND(p.price * s.btc, 0) AS market_cap_usd,ROUND(v.btc_volume * p.price, 0) AS tx_volume_usd,-- NVT = Arz / Günlük BTC Hacmi (fiyat iptal olur)ROUND(s.btc / NULLIF(v.btc_volume, 0), 2) AS nvt,-- 28 günlük NVT sinyali (daha kararlı)ROUND(AVG(s.btc / NULLIF(v.btc_volume, 0))OVER (ORDER BY p.day ROWS BETWEEN 27 PRECEDING AND CURRENT ROW), 2) AS nvt_ma28FROM price_daily pJOIN supply s ON TRUEJOIN tx_vol v ON v.day = p.dayORDER BY p.day