Query editor
WITH native_map (blockchain, wrapped) AS (VALUES('ethereum', 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2),('arbitrum', 0x82aF49447D8a07e3bd95BD0d56f35241523fBab1),('base', 0x4200000000000000000000000000000000000006),('optimism', 0x4200000000000000000000000000000000000006),('polygon', 0x0d500B1d8E8eF31E21C99d1Db9A6444d3ADf1270),('bnb', 0xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c),('gnosis', 0xe91D153E0b41518A2Ce8Dd3D7944Fa863463a97d),('linea', 0xe5D7C2a44FfDDf6b295A15c148167daaAf5Cf34f),('scroll', 0x5300000000000000000000000000000000000004),('zksync', 0x5AEa5775959fBC2557Cc8789bC1bf90A239D9a91),('unichain', 0x4200000000000000000000000000000000000006),('optimism', 0x4200000000000000000000000000000000000006),('sonic', 0x039e2fB66102314Ce7b64Ce5Ce3E5183bc94aD38),('berachain', 0x6969696969696969696969696969696969696969)),ev AS (SELECTchain,date_trunc('day', evt_block_time) AS d,sendingAssetId AS raw_token,CAST(amount AS double) AS raw_amt,bridgeFROM lifi_multichain.lifidiamond_evt_lifitransferstartedWHERE evt_block_time >= now() - INTERVAL '30' dayAND amount IS NOT NULL),resolved AS (SELECTe.chain, e.d, e.bridge, e.raw_amt,CASE WHEN e.raw_token IN (0x0000000000000000000000000000000000000000,0xEeeeeEeeeEeEeeEeEeEeeEEEeeeeEeeeeeeeEEeE)THEN n.wrapped ELSE e.raw_token END AS tokenFROM ev eLEFT JOIN native_map n ON n.blockchain = e.chain),agg AS (SELECT chain, d, token, bridge, SUM(raw_amt) AS raw_amt, COUNT(*) AS nFROM resolvedGROUP BY 1, 2, 3, 4),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),priced AS (SELECT a.chain, a.bridge, a.n,a.raw_amt / power(10, COALESCE(p.decimals, 18)) * p.price AS usd,CASE WHEN p.price IS NULL THEN a.n ELSE 0 END AS unpricedFROM agg aLEFT JOIN px p