Query editor
WITH daily_metrics AS (SELECTDATE_TRUNC('day', block_time) AS day,COUNT(DISTINCT id) AS unique_transactions,SUM(output_value) AS total_btc_transferredFROM bitcoin.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '30' DAYAND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND is_coinbase = falseGROUP BY 1),whale_transfers AS (SELECTDATE_TRUNC('day', block_time) AS day,COUNT(*) AS whale_transfer_count,SUM(output_value) AS whale_volume_btcFROM bitcoin.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '30' DAYAND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND is_coinbase = falseAND output_value > 100GROUP BY 1),fee_metrics AS (SELECTDATE_TRUNC('day', block_time) AS day,AVG(fee) AS avg_fee_btc,APPROX_PERCENTILE(fee, 0.5) AS median_fee_btcFROM bitcoin.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '30' DAYAND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND is_coinbase = falseGROUP BY 1),btc_price AS (SELECTDATE_TRUNC('day', timestamp) AS day,AVG(price) AS btc_price_usdFROM prices.dayWHERE blockchain = 'bitcoin'AND symbol = 'BTC'AND timestamp >= CURRENT_DATE - INTERVAL '30' DAYGROUP BY 1),joined_data AS (SELECTdm.day,dm.unique_transactions,dm.total_btc_transferred,COALESCE(wt.whale_transfer_count, 0) AS whale_transfer_count,COALESCE(wt.whale_volume_btc, 0) AS whale_volume_btc,fm.avg_fee_btc,fm.median_fee_btc,bp.btc_price_usd,fm.avg_fee_btc * bp.btc_price_usd AS avg_fee_usd,fm.median_fee_btc * bp.btc_price_usd AS median_fee_usdFROM daily_metrics dmLEFT JOIN whale_transfers wt ON dm.day = wt.dayLEFT JOIN fee_metrics fm ON dm.day = fm.dayLEFT JOIN btc_price bp ON dm.day = bp.day