Query editor
WITH withdrawals AS (SELECTevt_block_date AS block_date,chain,token,CAST(amount AS double) AS amount_raw,evt_tx_hashFROM aori_multichain.aori_evt_withdrawWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),withdrawals_priced AS (SELECTw.block_date,w.amount_raw / POWER(10, COALESCE(t.decimals, 18)) * COALESCE(p.price, 0) AS amount_usd,w.evt_tx_hashFROM withdrawals wLEFT JOIN tokens.erc20 tON t.blockchain = w.chain AND t.contract_address = w.tokenLEFT JOIN prices.usd_daily pON p.blockchain = w.chain AND p.contract_address = w.token AND p.day = w.block_date),deposits AS (SELECT call_block_date AS block_date, call_tx_hash AS tx_hashFROM aori_multichain.aori_call_depositWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAY AND call_success = true),fills AS (SELECT call_block_date AS block_date, call_tx_hash AS tx_hashFROM aori_multichain.aori_call_fillWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAY AND call_success = true),settles AS (SELECT call_block_date AS block_date, call_tx_hash AS tx_hashFROM aori_multichain.aori_call_settleWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAY AND call_success = true),hooks AS (SELECT evt_block_date AS block_date, evt_tx_hash AS tx_hashFROM aori_multichain.aori_evt_dsthookexecutedWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY)SELECTCOALESCE(v.block_date, dep.block_date, f.block_date) AS block_date,COALESCE(v.volume_usd, 0) AS volume_usd,COALESCE(dep.deposit_count, 0) AS deposit_count,COALESCE(f.fill_count, 0) AS fill_count,COALESCE(s.settle_count, 0) AS settle_count,COALESCE(h.hook_count, 0) AS dsthook_countFROM (SELECT block_date, SUM(amount_usd) AS volume_usdFROM withdrawals_priced GROUP BY 1) vFULL OUTER JOIN (SELECT block_date, COUNT(DISTINCT tx_hash) AS deposit_count FROM deposits GROUP BY 1) depON v.block_date = dep.block_dateFULL OUTER JOIN (SELECT block_date, COUNT(DISTINCT tx_hash) AS fill_count FROM fills GROUP BY 1) fON COALESCE(v.block_date, dep.block_date) = f.block_dateFULL OUTER JOIN (SELECT block_date, COUNT(DISTINCT tx_hash) AS settle_count FROM settles GROUP BY 1) sON COALESCE(v.block_date, dep.block_date, f.block_date) = s.block_dateFULL OUTER JOIN (SELECT block_date, COUNT(DISTINCT tx_hash) AS hook_count FROM hooks GROUP BY 1) hON COALESCE(v.block_date, dep.block_date, f.block_date, s.block_date) = h.block_date