kiacryptoshare of two tokens of each pools
Updated 2022-11-16Copy Reference Fork
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
›
⌄
with current_price as (
select token_address, avg(price) as price
from optimism.core.fact_hourly_token_prices
where hour::date = current_date - 1
group by 1
),
vol as (
select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, sum(token0_amount) as tok0_vol, sum(token1_amount) as tok1_vol
from optimism.velodrome.ez_lp_actions
where lp_action = 'deposit' and lp_token_amount_usd is not null
group by 1, 2, 3, 4, 5
union all
select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, -sum(token0_amount) as tok0_vol, -sum(token1_amount) as tok1_vol
from optimism.velodrome.ez_lp_actions
where lp_action = 'withdraw' and lp_token_amount_usd is not null
group by 1, 2, 3, 4, 5
),
liq as (
select pool_name, token0_address, token0_symbol, token1_address, token1_symbol, sum(tok0_vol) as tok0_liquidity, sum(tok1_vol) as tok1_liquidity
from vol
group by 1, 2, 3, 4, 5
)
select pool_name, token0_symbol as symbol, tok0_liquidity * price as vol_usd, 'token 0' as type
from liq, current_price
where token_address = token0_address
union all
select pool_name, token1_symbol as symbol, tok1_liquidity * price as vol_usd, 'token 1' as type
from liq, current_price
where token_address = token1_address
Run a query to Download Data