Query editor
WITH b AS (SELECT * FROM query_8958017 WHERE counted AND counterparty_chain IN ('ethereum','arbitrum','polygon')),ev AS (SELECT 'ethereum' AS chain, topic0, topic1, data, contract_address, block_time FROM ethereum.logs WHERE block_date >= DATE '2026-04-01' AND topic0 IN (keccak(to_utf8('OFTSent(bytes32,uint32,address,uint256,uint256)')), keccak(to_utf8('OFTReceived(bytes32,uint32,address,uint256)')))UNION ALL SELECT 'arbitrum', topic0, topic1, data, contract_address, block_time FROM arbitrum.logs WHERE block_date >= DATE '2026-04-01' AND topic0 IN (keccak(to_utf8('OFTSent(bytes32,uint32,address,uint256,uint256)')), keccak(to_utf8('OFTReceived(bytes32,uint32,address,uint256)')))UNION ALL SELECT 'polygon', topic0, topic1, data, contract_address, block_time FROM polygon.logs WHERE block_date >= DATE '2026-04-01' AND topic0 IN (keccak(to_utf8('OFTSent(bytes32,uint32,address,uint256,uint256)')), keccak(to_utf8('OFTReceived(bytes32,uint32,address,uint256)')))),j AS (SELECT b.direction, b.counterparty_chain, b.app, b.guid, b.usd_value, ev.contract_address,CASE WHEN ev.topic0 = keccak(to_utf8('OFTSent(bytes32,uint32,address,uint256,uint256)')) THEN 'OFTSent' ELSE 'OFTReceived' END AS evt,bytearray_to_uint256(bytearray_substring(ev.data, 33, 32)) AS amount_ld,bytearray_to_uint256(bytearray_substring(ev.data, 1, 32)) AS other_eidFROM b JOIN ev ON ev.chain = b.counterparty_chain AND ev.topic1 = from_hex(substr(b.guid, 3)))SELECT direction, counterparty_chain, app, evt, count(*) AS msgs, count(DISTINCT guid) AS guids,sum(CAST(amount_ld AS double)) / 1e6 AS token_amount_6dp, sum(usd_value) AS dune_usd,min(other_eid) AS min_eid, max(other_eid) AS max_eid,(SELECT count(*) FROM b b2 WHERE b2.direction = j.direction AND b2.counterparty_chain = j.counterparty_chain AND b2.app = j.app) AS base_msgs,(SELECT sum(usd_value) FROM b b2 WHERE b2.direction = j.direction AND b2.counterparty_chain = j.counterparty_chain AND b2.app = j.app) AS base_usdFROM j GROUP BY 1,2,3,4 ORDER BY dune_usd DESC NULLS LAST