WETH Pool Balances
In this example, we'll use the LiveQuery table function
tf_latest_token_balanceto retrieve the real-time balance of Uniswap Pools.
Let's start with a simple example, retrieve the balance of WETH and USDC on the WETH/USDC pool.
WETH Token Address:
USDC Token Address:
-- pool address
-- an array of addresses we want to know the pool's balance of
'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', -- WETH
'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
Note you must wrap the call to the function in
TABLE()since this function returns a table structure.
This function will always return a table with the following columns:
The function supports a few overloads. An overload means the function can take different combinations of inputs. Here are a few overload examples:
'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640', -- pool address
'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', -- WETH address
In this example, we'll pull the latest WETH balance for all WETH pools that have done greater than $10 million in volume over the past week.
We will first fetch the pools using Flipside's
ethereum.core.ez_dex_swapstable and then use the table function
ethereum_mainnet.tf_latest_token_balance()to retrieve the balance for each pool involving WETH.
WITH pools AS (
contract_address as pool_address,
sum(amount_out_usd) as volume_usd
-- filter to only pools involving `WETH`
(token_out = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') OR token_in = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'))
AND block_timestamp >= GETDATE() - interval '1 week'
AND amount_out_usd IS NOT NULL
-- filter to only pools with greater than $10m in USD volume
GROUP BY pool_address HAVING volume_usd >= 10000000
-- generate an array of pool addresses to pass to the function
(SELECT array_agg(distinct pool_address) FROM pools),
-- WETH token address
The above query will return the WETH balance for every pool that has had over $10 million in trading volume over the past week.