Query editor
-- Aori settlements (order completions)WITH settles AS (SELECT evt_block_date AS block_date, chain, evt_tx_hashFROM aori_multichain.aori_evt_settleWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),-- Aori fills (order fills)fills AS (SELECT call_block_date AS block_date, chain, call_tx_hash AS tx_hashFROM aori_multichain.aori_call_fillWHERE call_block_date >= CURRENT_DATE - INTERVAL '90' DAYAND call_success = true),-- Aori withdrawals (has amount + token for volume proxy)withdrawals AS (SELECTevt_block_date AS block_date,chain,evt_tx_hash,token,CAST(amount AS double) AS amount_rawFROM aori_multichain.aori_evt_withdrawWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),withdrawals_priced AS (SELECTw.block_date,w.evt_tx_hash,w.amount_raw / POWER(10, COALESCE(tk.decimals, 18)) * p.price AS amount_usdFROM withdrawals wLEFT JOIN tokens.erc20 tkON tk.blockchain = w.chain AND tk.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),daily_settles AS (SELECT block_date, COUNT(DISTINCT evt_tx_hash) AS settle_countFROM settles GROUP BY 1),daily_fills AS (SELECT block_date, COUNT(DISTINCT tx_hash) AS fill_countFROM fills GROUP BY 1),daily_withdrawals AS (SELECT block_date,COUNT(DISTINCT evt_tx_hash) AS withdrawal_count,COALESCE(SUM(amount_usd), 0) AS withdrawal_volume_usdFROM withdrawals_priced GROUP BY 1)SELECTCOALESCE(s.block_date, f.block_date, w.block_date) AS block_date,COALESCE(s.settle_count, 0) AS settle_count,COALESCE(f.fill_count, 0) AS fill_count,COALESCE(w.withdrawal_count, 0) AS withdrawal_count,COALESCE(w.withdrawal_volume_usd, 0) AS withdrawal_volume_usd,COALESCE(s.settle_count, 0) + COALESCE(f.fill_count, 0) AS total_activityFROM daily_settles sFULL OUTER JOIN daily_fills f ON s.block_date = f.block_dateFULL OUTER JOIN daily_withdrawals w ON COALESCE(s.block_date, f.block_date) = w.block_date