Query editor
WITH params AS (SELECT TIMESTAMP '2026-05-22' AS measure_date),btc_prices AS (SELECT date_trunc('day', minute) AS d, AVG(price) AS priceFROM prices.usdWHERE minute >= TIMESTAMP '2025-12-17' AND minute < TIMESTAMP '2026-05-23'AND symbol = 'BTC' AND blockchain IS NULLGROUP BY 1),current_price AS (SELECT price FROM btc_pricesWHERE d = (SELECT date_trunc('day', measure_date - INTERVAL '1' DAY) FROM params)),unspent_lt155 AS (SELECT date_trunc('day', o.block_time) AS creation_day, SUM(o.value) AS value_sumFROM bitcoin.outputs oLEFT JOIN bitcoin.inputs i ON i.spent_tx_id = o.tx_id AND i.spent_output_number = o.indexWHERE o.block_time >= (SELECT measure_date - INTERVAL '155' DAY FROM params)AND o.block_time < (SELECT measure_date FROM params)AND (i.block_time IS NULL OR i.block_time >= (SELECT measure_date FROM params))GROUP BY 1),agg AS (SELECT(SELECT price FROM current_price) AS cur_p,SUM(u.value_sum) AS sth_btc,SUM(u.value_sum * (SELECT price FROM current_price)) AS sth_market_value_usd,SUM(u.value_sum * pc.price) AS sth_realized_value_usd,SUM(u.value_sum * GREATEST((SELECT price FROM current_price) - pc.price, 0)) AS sth_unrealized_profit_usdFROM unspent_lt155 u JOIN btc_prices pc ON pc.d = u.creation_day)SELECT cur_p AS current_price, sth_btc, sth_market_value_usd,sth_realized_value_usd, sth_unrealized_profit_usd FROM agg