keshanLP Size Distribution
Updated 2022-05-02
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
›
⌄
with adding as (select pool_name, sum(rune_amount) as added_rune_amount, sum(rune_amount_usd) as added_rune_amount_usd,
sum(asset_amount) as added_asset_amount, sum(asset_amount_usd) as added_asset_amount_usd
from thorchain.liquidity_actions where lp_action = 'add_liquidity' group by pool_name),
removing as (select pool_name, sum(rune_amount) as removed_rune_amount, sum(rune_amount_usd) as removed_rune_amount_usd,
sum(asset_amount) as removed_asset_amount, sum(asset_amount_usd) as removed_asset_amount_usd
from thorchain.liquidity_actions where lp_action = 'remove_liquidity' group by pool_name),
ltv as (select a.pool_name, added_rune_amount, added_rune_amount_usd, added_asset_amount, added_asset_amount_usd, removed_rune_amount, removed_rune_amount_usd, removed_asset_amount, removed_asset_amount_usd,
(added_rune_amount - removed_rune_amount) as net_rune, (added_rune_amount_usd - removed_rune_amount_usd) as net_rune_usd, (added_asset_amount - removed_asset_amount) as net_assets, (added_asset_amount_usd - removed_asset_amount_usd) as net_assets_usd,
(net_rune + net_assets) net_ltv, (net_rune_usd + net_assets_usd) net_ltv_usd
from adding a full outer join removing using(pool_name))
select *,
(case when net_ltv_usd <= 0 then '$0'
when net_ltv_usd > 0 and net_ltv_usd <= 1e6 then '$0 - 1M'
when net_ltv_usd > 1e6 and net_ltv_usd <= 5 * 1e6 then '$1M - 5M'
when net_ltv_usd > 5 * 1e6 and net_ltv_usd <= 20 * 1e6 then '$5M - 20M'
when net_ltv_usd > 20 * 1e6 and net_ltv_usd <= 50 * 1e6 then '$20M - 50M'
when net_ltv_usd > 50 * 1e6 and net_ltv_usd <= 100 * 1e6 then '$50M - 100M'
when net_ltv_usd > 100 * 1e6 then '$100M+'
end) as dist
from ltv
Run a query to Download Data