Query editor
-- Karda Adres % (hafif versiyon)-- Son 90 günde aktif olan adreslerin güncel fiyat ile maliyet tabanı karşılaştırması.-- Tüm UTXO yerine yakın dönem aktivitesine odaklanır; community plan için optimize.WITHcur AS (SELECT AVG(price) AS priceFROM prices.usdWHERE symbol = 'BTC' AND blockchain IS NULLAND minute >= NOW() - INTERVAL '1' hour),price_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),-- Son 90 günde oluşan çıktılar, harcanmamış olanlar (anti-join, kısa pencere)unspent_recent AS (SELECTo.address,CAST(o.value AS DOUBLE) / 1e8 AS btc_amount,DATE_TRUNC('day', o.block_time) AS creation_dayFROM bitcoin.outputs oLEFT JOIN bitcoin.inputs iON i.spent_tx_id = o.tx_idAND i.spent_output_number = o.indexWHERE o.block_date >= current_date - INTERVAL '90' dayAND o.value > 546 -- dust filtresiAND o.address IS NOT NULLAND i.tx_id IS NULL -- harcanmamış),basis AS (SELECTu.address,SUM(u.btc_amount * p.price) / NULLIF(SUM(u.btc_amount), 0) AS avg_costFROM unspent_recent uJOIN price_daily p ON p.day = u.creation_dayGROUP BY u.addressHAVING SUM(u.btc_amount) > 0)SELECTCURRENT_DATE AS date,(SELECT price FROM cur) AS current_price,COUNT(*) AS total_addresses,COUNT(CASE WHEN b.avg_cost <= (SELECT price FROM cur) THEN 1 END) AS addresses_in_profit,ROUND(100.0 * COUNT(CASE WHEN b.avg_cost <= (SELECT price FROM cur) THEN 1 END)/ NULLIF(COUNT(*), 0), 2) AS pct_in_profitFROM basis b