theericstone🏊♂️ pool balances with livequery ⚡️
Updated 2023-12-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- find pools by token pairs
with pools as (
select *, tokens:token0::varchar as token0,
tokens:token1::varchar as token1 FROM
ethereum.defi.dim_dex_liquidity_pools
where (
tokens:token0 = lower('{{token_0}}')
and tokens:token1 = lower('{{token_1}}')
) or (
tokens:token1 = lower('{{token_0}}')
and tokens:token0 = lower('{{token_1}}')
)
),
-- use livequery to pull live balance data for
-- every pool's token0
pool0 as (
select
current_timestamp as time,
wallet_address as pool_address,
token_address as token0,
symbol as symbol0,
balance as balance0
from table(
ethereum_mainnet.tf_latest_token_balance(
(select array_agg(pool_address) from pools),
(select array_agg(token0) from pools)
)
) where balance > 0
),
-- now get the same thing for all token1s
pool1 as (
select
wallet_address as pool_address,
token_address as token1,
QueryRunArchived: QueryRun has been archived