-- forked from Bridge CollectedFees by Chain @ https://flipsidecrypto.xyz/edit/queries/73b55534-9230-41a0-b270-ef77191dc4bd with all_txns as ( SELECT *, 'arbitrum' as src_chain FROM arbitrum.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'optimism' as src_chain FROM optimism.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'bnb smart chain (bep20)' as src_chain FROM bsc.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'base' as src_chain FROM base.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'ethereum' as src_chain FROM ethereum.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'polygon' as src_chain FROM polygon.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'gnosis' as src_chain FROM gnosis.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'avalanche' as src_chain FROM avalanche.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' UNION ALL SELECT *, 'blast' as src_chain FROM blast.core.ez_decoded_event_logs WHERE block_timestamp >= '2025-02-01' --AND block_timestamp <= '2025-02-19' ) SELECT to_date(block_timestamp) as date, src_chain, -- b.symbol, -- b.blockchain, -- sum(a.decoded_log:_integratorFee * b.price * pow(10, -b.decimals)) as integrator_fee_usd, -- sum(a.decoded_log:_lifiFee * b.price * pow(10, -b.decimals)) as lifi_fee_usd count(*) FROM all_txns -- Join crosschain.price.ez_prices_hourly b -- ON b.token_address = a.decoded_log:_token -- AND b.hour = date_trunc("hour",a.block_timestamp) -- AND b.blockchain = a.src_chain WHERE event_name = 'FeesCollected' AND block_timestamp < '2025-02-20' AND contract_address IN ( '0xb0210de78e28e2633ca200609d9f528c13c26cd9',--arb,ava '0xbd6c7b0d2f68c2b7805d88388319cfb6ecb50ea9',--gno,eth,op,bsc,pol '0x0a6d96e7f4d7b96cfe42185df61e64d255c12dff',--base '0xF048e5816B0C7951AC179f656C5B86e5a79Bd7b5'--blast ) group by 1,2 order by 1

jivala