binhachonLP Size Distribution - LP value
Updated 2022-04-29
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
›
⌄
with stake_units as (
select
pool_name,
coalesce(from_address, asset_address) as address,
sum(case when lp_action = 'add_liquidity' then stake_units else - stake_units end) as stake_units
from thorchain.liquidity_actions
group by 1, 2
),
pool_distribution as (
select
pool_name,
address,
stake_units,
100 * ratio_to_report(stake_units) over (partition by pool_name) as percent
from stake_units
where stake_units > 0
),
pool_stats as (
select
pool_name,
rune_liquidity * rune_price_usd + asset_liquidity * asset_price_usd as liquidity_usd
from thorchain.daily_pool_stats
qualify row_number() over (partition by pool_name order by day desc) = 1
)
select
pool_distribution.pool_name,
floor(percent, 0) as rounded_percent,
liquidity_usd,
sum(percent) as total_percent,
total_percent * liquidity_usd / 100 as liquidity_usd_total
from pool_distribution
left join pool_stats on (pool_distribution.pool_name = pool_stats.pool_name)
group by 1, 2, 3
Run a query to Download Data