Query editor
WITH timeframe_config AS (SELECTCASEWHEN '{{Timeframe}}' = 'Daily' THEN 'day'WHEN '{{Timeframe}}' = 'Weekly' THEN 'week'WHEN '{{Timeframe}}' = 'Monthly' THEN 'month'ELSE 'day'END AS period,CASEWHEN '{{Timeframe}}' = 'Daily' THEN CAST(NOW() - INTERVAL '90' DAY AS date)WHEN '{{Timeframe}}' = 'Weekly' THEN CAST(NOW() - INTERVAL '1' YEAR AS date)WHEN '{{Timeframe}}' = 'Monthly' THEN CAST('2020-01-01' AS date)ELSE CAST(NOW() - INTERVAL '90' DAY AS date)END AS start_date),activity_metrics AS (SELECTdate_trunc(tc.period, t.block_time) AS time,COUNT(*) AS transaction_count,SUM(t.output_value) AS volume_btc,COUNT(*) AS unique_transactions -- Same as transaction_count since each row is uniqueFROM bitcoin.transactions tCROSS JOIN timeframe_config tcWHERE date_trunc('day', t.block_time) >= tc.start_dateGROUP BY 1, tc.period),address_metrics AS (SELECTdate_trunc(tc.period, block_time) AS time,APPROX_DISTINCT(address) AS approx_active_addressesFROM (SELECT block_time, addressFROM bitcoin.inputs iCROSS JOIN timeframe_config tcWHERE date_trunc('day', i.block_time) >= tc.start_dateUNION ALLSELECT block_time, addressFROM bitcoin.outputs oCROSS JOIN timeframe_config tcWHERE date_trunc('day', o.block_time) >= tc.start_dateAND o.value > 0) all_addressesCROSS JOIN timeframe_config tcGROUP BY 1, tc.period),price_data AS (SELECTdate_trunc(tc.period, p.minute) AS time,AVG(p.price) AS avg_btc_priceFROM prices.usd pCROSS JOIN timeframe_config tcWHERE p.contract_address IS NULLAND p.symbol = 'BTC'AND date_trunc('day', p.minute) >= tc.start_date