Query editor
WITHunlisted_tokens AS (SELECT * FROM query_3806957),blacklist_tokens AS (SELECT * FROM query_3806980),erc20 AS (SELECT contract_address, symbol, decimalsFROM (SELECT DISTINCT t.contract_address, t.symbol, t.decimalsFROM tokens.erc20 tLEFT JOIN blacklist_tokens fk ON t.contract_address = fk.contract_addressWHERE blockchain = 'base'AND fk.contract_address IS NULLAND (t.decimals != 8 OR t.symbol IN ('tBTC', 'axlWBTC', 'BTCB', 'cbBTC'))UNIONSELECT ut.contract_address, ut.symbol, ut.decimalsFROM unlisted_tokens utLEFT JOIN blacklist_tokens fk ON ut.contract_address = fk.contract_addressWHERE fk.contract_address IS NULL)),pairs_legacy AS (SELECTevt_block_time AS block_time,evt_tx_hash AS tx_hash,token0,token1, pairId,CAST(token0 as varchar) || '_' || CAST(token1 as varchar) as native_pair,LEAST(CAST(token0 as varchar), CAST(token1 as varchar)) || '_' || GREATEST(CAST(token0 as varchar), CAST(token1 as varchar)) AS pair_alphaFROM basevelocimeter_base.CarbonController_evt_PairCreated),aerodrome_pairs AS (SELECTgauge.evt_block_time AS block_time,DATE_TRUNC('day', gauge.evt_block_time) AS time_stamp,gauge.evt_tx_hash AS tx_hash,gauge.pool AS pool_contract,pair.pool_ticker, pair.stable,pair."token0" AS token0_contract,pair."token1" AS token1_contract,pair.token0_symbol, pair.token1_symbol,pair.token0_decimals, pair.token1_decimalsFROM aerodrome_base.Voter_evt_GaugeCreated gaugeLEFT JOIN (SELECT"token0", t0.symbol AS token0_symbol, t0.decimals AS token0_decimals,"token1", t1.symbol AS token1_symbol, t1.decimals AS token1_decimals,stable, pool,CASEWHEN stable THEN 'sAMM-' || t1.symbol || '/' || t0.symbolELSE 'vAMM-' || t1.symbol || '/' || t0.symbolEND AS pool_ticker,t1.symbol || '-' || t0.symbol AS token_pairFROM aerodrome_base.PoolFactory_evt_PoolCreated pLEFT JOIN erc20 t0 ON t0.contract_address = p."token0"LEFT JOIN erc20 t1 ON t1.contract_address = p."token1") pair ON gauge.pool = pair.pool