Query editor
-- LTH / STH Realized Price — Harcanan UTXO yaklaşımı (anti-join yok)-- Anti-join community plan'da timeout alır.-- Yaklaşım: Son 60 günde harcanan çıktıları holding süresine göre LTH/STH ayır.-- LTH cost basis: 155+ gün tutulup son 60 günde harcanan coin'ler-- STH cost basis: <155 gün tutulup son 60 günde harcanan coin'lerWITHprice_daily AS (SELECT DATE_TRUNC('day', minute) AS day, AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC' AND blockchain IS NULLAND minute >= NOW() - INTERVAL '700' dayGROUP BY 1),cur AS (SELECT AVG(price) AS price FROM prices.usdWHERE symbol = 'BTC' AND blockchain IS NULLAND minute >= NOW() - INTERVAL '1' hour),-- Son 60 günde harcanan çıktılar (inputs JOIN outputs, kısa pencere = hızlı)spent AS (SELECTDATE_TRUNC('day', o.block_time) AS creation_day,CAST(o.value AS DOUBLE) / 1e8 AS btc_amount,date_diff('day', o.block_time, i.block_time) AS hold_daysFROM bitcoin.inputs iJOIN bitcoin.outputs oON i.spent_tx_id = o.tx_idAND i.spent_output_number = o.indexWHERE i.block_date >= current_date - INTERVAL '60' dayAND o.value > 0),with_prices AS (SELECTs.hold_days,s.btc_amount,p.price AS creation_price,s.btc_amount * p.price AS realized_valueFROM spent sJOIN price_daily p ON p.day = s.creation_day),lth AS (SELECTSUM(btc_amount) AS lth_btc,SUM(realized_value) AS lth_realized_capFROM with_prices WHERE hold_days >= 155),sth AS (SELECTSUM(btc_amount) AS sth_btc,SUM(realized_value) AS sth_realized_capFROM with_prices WHERE hold_days < 155 AND hold_days >= 0)SELECTCURRENT_DATE AS date,(SELECT price FROM cur) AS current_price,COALESCE(l.lth_btc, 0) AS lth_btc,ROUND(l.lth_realized_cap / NULLIF(l.lth_btc, 0), 2) AS lth_realized_price,ROUND((SELECT price FROM cur) / NULLIF(l.lth_realized_cap / NULLIF(l.lth_btc, 0), 0), 4) AS lth_mvrv,COALESCE(s.sth_btc, 0) AS sth_btc,ROUND(s.sth_realized_cap / NULLIF(s.sth_btc, 0), 2) AS sth_realized_price,