Query editor
WITH-- Define key addresses and parameterskeys_ca AS (SELECT0x548f93779fBC992010C07467cBaf329DD5F059B7 AS bmx_token,18 AS bmx_decimals, -- Define BMX token decimals0x4200000000000000000000000000000000000006 AS base_weth -- Canonical WETH on Base),-- Combine trades from dex, kyber, and odos, normalizing ETH addresses and adding a source identifierall_trades_combined AS (-- dex.tradesSELECTtx_hash,evt_index,block_date,block_time,taker,-- Normalize ETH addressesCASE WHEN token_bought_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_bought_address END AS token_bought_address,CASE WHEN token_sold_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_sold_address END AS token_sold_address,token_bought_amount,token_sold_amount,amount_usd,project,'dex' AS source -- Add source identifierFROM dex.tradesCROSS JOIN keys_ca kWHERE blockchain = 'base'AND (CASE WHEN token_bought_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_bought_address END = k.bmx_tokenORCASE WHEN token_sold_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_sold_address END = k.bmx_token)UNION ALL-- kyberswap_aggregator.tradesSELECTtx_hash,evt_index,block_date,block_time,taker,-- Normalize ETH addressesCASE WHEN token_bought_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_bought_address END AS token_bought_address,CASE WHEN token_sold_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_sold_address END AS token_sold_address,token_bought_amount,token_sold_amount,amount_usd,project,'kyber' AS source -- Add source identifierFROM kyberswap_aggregator.tradesCROSS JOIN keys_ca kWHERE blockchain = 'base'AND (CASE WHEN token_bought_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_bought_address END = k.bmx_tokenORCASE WHEN token_sold_address IN (0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee, 0x0000000000000000000000000000000000000000) THEN k.base_weth ELSE token_sold_address END = k.bmx_token