Query editor
WITH daily_data AS (SELECTdate_trunc('day', block_time) AS day,SUM(output_value) as volume_btc,COUNT(*) as transaction_countFROM bitcoin.transactionsWHERE block_time >= NOW() - INTERVAL '180' dayGROUP BY 1),price_data AS (SELECTdate_trunc('day', minute) as day,AVG(price) as btc_priceFROM prices.usdWHERE contract_address IS NULLAND symbol = 'BTC'AND minute >= NOW() - INTERVAL '180' dayGROUP BY 1),daily_with_moving_averages AS (SELECTd.day,d.volume_btc,d.transaction_count,p.btc_price,d.volume_btc * p.btc_price as volume_usd,-- All moving averages calculated in one placeAVG(d.volume_btc) OVER (ORDER BY d.day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_30d_volume_btc,AVG(d.volume_btc * p.btc_price) OVER (ORDER BY d.day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_30d_volume_usd,AVG(d.transaction_count) OVER (ORDER BY d.day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_30d_transactions,AVG(d.volume_btc) OVER (ORDER BY d.day ROWS BETWEEN 179 PRECEDING AND CURRENT ROW) as avg_180d_volume_btc,AVG(d.volume_btc * p.btc_price) OVER (ORDER BY d.day ROWS BETWEEN 179 PRECEDING AND CURRENT ROW) as avg_180d_volume_usd,AVG(d.transaction_count) OVER (ORDER BY d.day ROWS BETWEEN 179 PRECEDING AND CURRENT ROW) as avg_180d_transactionsFROM daily_data dJOIN price_data p ON d.day = p.day)SELECTday,-- Daily metricsROUND(volume_btc, 2) as daily_volume_btc,ROUND(volume_usd / 1e6, 2) as daily_volume_millions_usd,transaction_count as daily_transactions,-- 30-day averagesROUND(avg_30d_volume_btc, 2) as avg_30d_volume_btc,ROUND(avg_30d_volume_usd / 1e6, 2) as avg_30d_volume_millions_usd,ROUND(avg_30d_transactions) as avg_30d_transactions,-- 6-month averagesROUND(avg_180d_volume_btc, 2) as avg_6month_volume_btc,ROUND(avg_180d_volume_usd / 1e6, 2) as avg_6month_volume_millions_usd,ROUND(avg_180d_transactions) as avg_6month_transactions,-- Performance vs averages