Query editor
with amm as (WITH markets as (SELECTfixedProductMarketMaker AS market_address, collateralToken AS collateralFROM limitless_base.FixedProductMarketMakerFactory_evt_FixedProductMarketMakerCreationUNION ALLSELECTfixedProductMarketMaker AS market_address, collateralToken AS collateralFROM limitless_exchange_prod_hanson_v0_1_base.FixedProductMarketMakerFactory_evt_FixedProductMarketMakerCreation),collaterals AS (SELECT minute, contract_address, decimals, AVG(price) as priceFROM prices.usdWHERE blockchain = 'base'and contract_address IN (SELECT collateral FROM markets)GROUP BY 1,2,3),dexprices as (SELECT block_date, AVG(price) as price, address,CASEWHEN address = 0xcd2f22236dd9dfe2356d7c543161d4d260fd9bcb THEN 18WHEN address = 0x490a4b510d0ea9f835d2df29eb73b4fca5071937 THEN 18WHEN address = 0xfef2d7b013b88fec2bfe4d2fee0aeb719af73481 THEN 18WHEN address = 0x4e65fe4dba92790696d040ac24aa414708f5c0ab THEN 6END AS decimalsFROM (SELECT block_date, amount_usd/token_bought_amount as price, token_bought_address as address FROM dex.tradesWHERE token_bought_address IN (0xfef2d7b013b88fec2bfe4d2fee0aeb719af73481, 0x4e65fe4dba92790696d040ac24aa414708f5c0ab, 0xcd2f22236dd9dfe2356d7c543161d4d260fd9bcb, 0x490a4b510d0ea9f835d2df29eb73b4fca5071937)AND blockchain = 'base'AND amount_usd IS NOT NULLUNION ALLSELECT block_date, amount_usd/token_sold_amount as price, token_sold_address as address FROM dex.tradesWHERE token_sold_address IN (0xfef2d7b013b88fec2bfe4d2fee0aeb719af73481, 0x4e65fe4dba92790696d040ac24aa414708f5c0ab, 0xcd2f22236dd9dfe2356d7c543161d4d260fd9bcb, 0x490a4b510d0ea9f835d2df29eb73b4fca5071937)AND blockchain = 'base'AND amount_usd IS NOT NULL)GROUP BY 1,3)SELECTDATE_TRUNC('day', block_time) as "Day",SUM(bytearray_to_uint256(substr(DATA, 1, 32)) / POW(10, COALESCE(c.decimals, d.decimals))*COALESCE(c.price, d.price, 1000000)) as VolumeFROM base.logs bINNER JOIN markets m ON b.contract_address = m.market_addressLEFT JOIN collaterals c ON m.collateral = c.contract_address AND DATE_TRUNC('minute', b.block_time) = c.minuteLEFT JOIN dexprices d ON m.collateral = d.address AND DATE_TRUNC('day', b.block_time) = d.block_dateWHERE topic0 IN (0x4f62630f51608fc8a7603a9391a5101e58bd7c276139366fc107dc3b67c3dcf8, 0xadcf2a240ed9300d681d9a3f5382b6c1beed1b7e46643e0c7b42cbe6e2d766b4)GROUP BY 1)SELECT "Day", SUM("Volume"/2) as "Volume", SUM(SUM("Volume")/2) OVER (ORDER BY "Day") as "Cumulative Volume" FROM (SELECT DATE_TRUNC('day', evt_block_time) as "Day",CASEWHEN CAST(makerAssetId AS VARCHAR) = '0' THEN CAST(makerAmountFilled AS DOUBLE) / 1e6WHEN CAST(takerAssetId AS VARCHAR) = '0' THEN CAST(takerAmountFilled AS DOUBLE) / 1e6END AS "Volume"FROM limitless_base.ctfexchange_evt_orderfilledUNION ALL