Query editor
WITH fills AS (SELECTcall_block_date AS block_date,chain,'fill' AS event_type,call_tx_hash AS tx_hashFROM aori_multichain.aori_call_fillWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAYAND call_success = true),deposits AS (SELECTcall_block_date AS block_date,chain,'deposit' AS event_type,call_tx_hash AS tx_hashFROM aori_multichain.aori_call_depositWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAYAND call_success = true),-- Use withdrawal events for volume estimation (has token + amount)withdrawals_priced AS (SELECTevt_block_date AS block_date,chain,CAST(amount AS double) / POWER(10, COALESCE(t.decimals, 18)) * p.price AS amount_usd,evt_tx_hash AS tx_hashFROM aori_multichain.aori_evt_withdraw wLEFT JOIN tokens.erc20 t ON t.blockchain = w.chain AND t.contract_address = w.tokenLEFT JOIN prices.usd_daily p ON p.blockchain = w.chain AND p.contract_address = w.token AND p.day = w.evt_block_dateWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY)SELECTCOALESCE(f.block_date, w.block_date) AS block_date,COALESCE(f.fill_count, 0) AS fill_count,COALESCE(f.deposit_count, 0) AS deposit_count,COALESCE(w.withdrawal_volume_usd, 0) AS estimated_volume_usdFROM (SELECTblock_date,COUNT(DISTINCT CASE WHEN event_type = 'fill' THEN tx_hash END) AS fill_count,COUNT(DISTINCT CASE WHEN event_type = 'deposit' THEN tx_hash END) AS deposit_countFROM (SELECT * FROM fills UNION ALL SELECT * FROM deposits) xGROUP BY 1) fFULL OUTER JOIN (SELECTblock_date,COALESCE(SUM(amount_usd), 0) AS withdrawal_volume_usdFROM withdrawals_pricedGROUP BY 1) w ON f.block_date = w.block_dateORDER BY 1