Query editor
WITH pool_address AS (SELECT 0xf9349c5af43d2abc2758e90cfb341722116fac38 AS addr),-- Get weETH price from Ethereum mainnetweeth_price AS (SELECT price AS weeth_usd_priceFROM prices.latestWHERE blockchain = 'ethereum'AND contract_address = 0xCd5fE23C85820F7B72D0926FC9b05b43E359b7eeLIMIT 1),-- Get ETH/WETH priceeth_price AS (SELECT price AS eth_usd_priceFROM prices.latestWHERE blockchain = 'ethereum'AND contract_address = 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2LIMIT 1),-- Calculate token balances by summing all transfers TO and FROM the pool-- This is the most accurate way to get current pool holdingstoken_balances AS (SELECTcontract_address AS token_address,SUM(CASEWHEN "to" = (SELECT addr FROM pool_address) THEN CAST(value AS DOUBLE)WHEN "from" = (SELECT addr FROM pool_address) THEN -CAST(value AS DOUBLE)ELSE 0END) / 1e18 AS balanceFROM erc20_ink.evt_TransferWHERE "to" = (SELECT addr FROM pool_address)OR "from" = (SELECT addr FROM pool_address)GROUP BY contract_address),-- Identify which token is which based on known addresses or by checking balances-- On OP Stack chains, WETH is typically 0x4200000000000000000000000000000000000006pool_balances AS (SELECTCOALESCE(MAX(CASE WHEN token_address != 0x4200000000000000000000000000000000000006 THEN balance END), 0) AS weETH_balance,COALESCE(MAX(CASE WHEN token_address = 0x4200000000000000000000000000000000000006 THEN balance END), 0) AS WETH_balanceFROM token_balancesWHERE balance > 0)SELECT-- Token balancespb.weETH_balance AS weETH_tvl,pb.WETH_balance AS WETH_tvl,-- Priceswp.weeth_usd_price,ep.eth_usd_price,-- USD valuespb.weETH_balance * wp.weeth_usd_price AS weETH_tvl_usd,