Query editor
-- SOPR — bitcoin.inputs JOIN bitcoin.outputs-- Dune'da spent_block_time yok; bitcoin.inputs tablosundan harcama zamanı alınır.WITH price_daily AS (SELECTDATE_TRUNC('day', minute) AS day,AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC'AND blockchain IS NULLAND minute >= NOW() - INTERVAL '500' dayGROUP BY 1),-- Son 180 günde harcanan çıktılar (daha geniş aralık sorguyu yavaşlatır)spent AS (SELECTDATE_TRUNC('day', i.block_time) AS spent_day,DATE_TRUNC('day', o.block_time) AS created_day,CAST(o.value AS DOUBLE) AS satsFROM bitcoin.inputs iJOIN bitcoin.outputs oON i.spent_tx_id = o.tx_idAND i.spent_output_number = o.indexWHERE i.block_time >= NOW() - INTERVAL '180' dayAND o.value > 0),joined AS (SELECTs.spent_day,s.sats * p_spend.price AS val_spent,s.sats * p_create.price AS val_createdFROM spent sJOIN price_daily p_spend ON p_spend.day = s.spent_dayJOIN price_daily p_create ON p_create.day = s.created_dayWHERE p_create.price > 0)SELECTspent_day AS date,SUM(val_spent) / NULLIF(SUM(val_created), 0) AS sopr,COUNT(*) AS output_countFROM joinedGROUP BY 1ORDER BY 1