Query editor
-- SOPR — Günlük harcanan çıktıların harcandığı fiyat / oluşturulduğu fiyat oranı-- Düşük güvenilirlik: price join büyük tabloda yavaş çalışabilir-- Önerilen: Son 365 güne sınırlaWITH 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),spent AS (SELECTDATE_TRUNC('day', o.spent_block_time) AS spent_day,DATE_TRUNC('day', o.block_time) AS created_day,CAST(o.value AS DOUBLE) AS satsFROM bitcoin.outputs oWHERE o.spent_block_time >= NOW() - INTERVAL '365' dayAND o.spent_block_time IS NOT NULLAND 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_day)SELECTspent_day AS date,SUM(val_spent) / NULLIF(SUM(val_created), 0) AS sopr,COUNT(*) AS output_countFROM joinedGROUP BY 1ORDER BY 1