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 NULLUNIONSELECT 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)),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.poolWHERE pair.token0_symbol IS NOT NULL AND pair.token1_symbol IS NOT NULL),aerodrome_volumes AS (SELECTv.day, v.block_time, v.tx_hash,p.stable, p.pool_contract, p.pool_ticker,p.token0_symbol, p.token1_symbol, p.token0_contract, p.token1_contract,CASE WHEN v.amount0Out = CAST(0 AS uint256)THEN (v.amount0In / POW(10, p.token0_decimals)) / (v.amount1Out / POW(10, p.token1_decimals))ELSE (v.amount1In / POW(10, p.token1_decimals)) / (v.amount0Out / POW(10, p.token0_decimals))END AS token0_price,