Query editor
WITH spellbook_transfers AS (SELECTblock_date,tx_hash,bridge,amount_usd,source_chainFROM lifi.transfersWHERE block_date >= CURRENT_DATE - INTERVAL '90' DAY),decoded_v1 AS (SELECTevt_block_date AS block_date,chain,evt_tx_hash AS tx_hashFROM lifi_multichain.lifidiamond_evt_lifitransferstartedWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),decoded_v2 AS (SELECTevt_block_date AS block_date,chain,evt_tx_hash AS tx_hashFROM lifi_multichain.lifidiamond_v2_evt_lifitransferstartedWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),all_decoded_txs AS (SELECT block_date, CAST(tx_hash AS varchar) AS tx_id FROM decoded_v1UNIONSELECT block_date, CAST(tx_hash AS varchar) FROM decoded_v2)SELECTCOALESCE(d.block_date, s.block_date) AS block_date,COALESCE(d.total_tx_count, 0) AS total_tx_count,COALESCE(s.spellbook_tx_count, 0) AS spellbook_tx_count,COALESCE(s.daily_volume_usd, 0) AS daily_volume_usd,SUM(COALESCE(s.daily_volume_usd, 0)) OVER (ORDER BY COALESCE(d.block_date, s.block_date)) AS cumulative_volume_usdFROM (SELECT block_date, COUNT(DISTINCT tx_id) AS total_tx_countFROM all_decoded_txs GROUP BY 1) dFULL OUTER JOIN (SELECT block_date, COUNT(DISTINCT tx_hash) AS spellbook_tx_count, SUM(amount_usd) AS daily_volume_usdFROM spellbook_transfers GROUP BY 1) s ON d.block_date = s.block_dateORDER BY 1