Query editor
WITH native_map (blockchain, wrapped) AS (VALUES('ethereum', 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2),('arbitrum', 0x82aF49447D8a07e3bd95BD0d56f35241523fBab1),('base', 0x4200000000000000000000000000000000000006),('optimism', 0x4200000000000000000000000000000000000006),('polygon', 0x0d500B1d8E8eF31E21C99d1Db9A6444d3ADf1270),('bnb', 0xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c),('avalanche_c', 0xB31f66AA3C1e785363F0875A1B74E27b85FD66c7),('gnosis', 0xe91D153E0b41518A2Ce8Dd3D7944Fa863463a97d),('fantom', 0x21be370D5312f44cB42ce377BC9b8a0cEF1A4C83),('linea', 0xe5D7C2a44FfDDf6b295A15c148167daaAf5Cf34f),('scroll', 0x5300000000000000000000000000000000000004),('zksync', 0x5AEa5775959fBC2557Cc8789bC1bf90A239D9a91),('blast', 0x4300000000000000000000000000000000000004),('zora', 0x4200000000000000000000000000000000000006),('celo', 0x471EcE3750Da237f93B8E339c536989b8978a438)),ev AS (SELECTchain,date_trunc('day', evt_block_time) AS d,bridgeToken AS raw_token,CAST(bridgeAmount AS double) AS raw_amtFROM rango_exchange_multichain.rangodiamond_evt_rangobridgeinitiatedWHERE evt_block_time >= now() - INTERVAL '30' day),resolved AS (SELECTe.chain,e.d,CASE WHEN e.raw_token IN (0x0000000000000000000000000000000000000000,0xEeeeeEeeeEeEeeEeEeEeeEEEeeeeEeeeeeeeEEeE)THEN n.wrapped ELSE e.raw_token END AS token,e.raw_amtFROM ev eLEFT JOIN native_map n ON n.blockchain = e.chain),agg AS (SELECT chain, d, token, SUM(raw_amt) AS raw_amt, COUNT(*) AS nFROM resolvedGROUP BY 1, 2, 3),px AS (SELECT blockchain, date_trunc('day', timestamp) AS d, contract_address,AVG(price) AS price, MAX(decimals) AS decimalsFROM prices.dayWHERE timestamp >= now() - INTERVAL '31' dayGROUP BY 1, 2, 3)SELECTa.chain,SUM(a.n) AS bridge_events_30d,SUM(CASE WHEN p.price IS NULL THEN a.n ELSE 0 END) AS unpriced_events,SUM(a.raw_amt / power(10, COALESCE(p.decimals, 18)) * p.price) AS usd_30d