Query editor
withtokens_data AS (SELECTpool_contract, pool_symbol, isStable,token0_contract, token1_contract,token0_symbol, token1_symbol,token0_decimals, token1_decimalsFROM query_3807657),pools_data AS (SELECTfl.block_date, fl.tx_hash,t.pool_contract, t.isStable, t.pool_symbol,fl.amount0Out = CAST(0 AS uint256) isZeroOut,fl.amount0In / POW(10, t.token0_decimals) AS amount0In_scaled,fl.amount0Out / POW(10, t.token0_decimals) AS amount0Out_scaled,fl.amount1In / POW(10, t.token1_decimals) AS amount1In_scaled,fl.amount1Out / POW(10, t.token1_decimals) AS amount1Out_scaled,t.token0_symbol, t.token1_symbol, t.token0_contract, t.token1_contractFROM (SELECTblock_date, tx_hash, contract_address,bytearray_to_uint256(bytearray_substring(data,2,31)) AS amount0In,bytearray_to_uint256(bytearray_substring(data,2+32,31)) AS amount1In,bytearray_to_uint256(bytearray_substring(data,2+32+32,31)) AS amount0Out,bytearray_to_uint256(bytearray_substring(data,2+32+32+32,31)) AS amount1OutFROM mantle.logs flWHERE topic0 = 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822AND contract_address IN (SELECT pool_contractFROM tokens_data)) flLEFT JOIN tokens_data t ON t.pool_contract = fl.contract_addressWHERE (fl.amount0In > 0 AND fl.amount1Out > 0) OR (fl.amount1In > 0 AND fl.amount0Out > 0)),wmnt_price AS (SELECT AVG(price) AS avg_price, DATE_TRUNC('day', minute) AS truncated_timeFROM prices.usdWHERE symbol = 'MNT' AND blockchain = 'ethereum'GROUP BY DATE_TRUNC('day', minute)),weth_price AS (SELECT AVG(price) AS avg_price, DATE_TRUNC('day', minute) AS truncated_time,contract_addressFROM prices.usdWHERE symbol = 'WETH' AND blockchain = 'ethereum'GROUP BY DATE_TRUNC('day', minute),3),price_feed AS (SELECTblock_date, tx_hash, isStable, token0_symbol, token1_symbol, token0_contract, token1_contract, token0_amount, token1_amount,CASE WHEN isStable = false THENCASEWHEN token0_symbol = 'WMNT' THEN wmnt_price.avg_priceWHEN token1_symbol = 'WMNT' THEN wmnt_price.avg_price * pf.token0_priceWHEN token0_symbol != 'WMNT' AND token1_symbol != 'WMNT' THEN AVG(pf.token0_price)ENDELSECASEWHEN token0_symbol = 'WETH' THEN weth_price.avg_price