binhachon17. [Hard] Pool Depth - Rank table - Swim with the popular
Updated 2021-11-08
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
›
⌄
with pool_depth as(
select day, pool_name, rune_liquidity*rune_price_usd*2 as pool_depth, row_number() over (partition by pool_name order by day desc) as row_number from thorchain.daily_pool_stats
qualify row_number = 1
),
address_list as(
select address, pool_name, sum(amount_provided) - sum(amount_withdrawed) as net_provided from(
select from_address as address, sum(asset_amount_usd + rune_amount_usd) as amount_provided, 0 as amount_withdrawed, pool_name from thorchain.liquidity_actions
where lp_action = 'add_liquidity'
group by address, pool_name
union all
select from_address as address, 0 as amount_provided, sum(asset_amount_usd + rune_amount_usd) as amount_withdrawed, pool_name from thorchain.liquidity_actions
where lp_action = 'remove_liquidity'
group by address, pool_name
)
group by address, pool_name
),
number_of_addresses as(
select pool_name, count(address) as number_of_addresses from address_list
where net_provided > 0
group by pool_name
),
final_table as(
select pool_depth.pool_name, pool_depth, number_of_addresses from pool_depth left join number_of_addresses on pool_depth.pool_name = number_of_addresses.pool_name
)
select case when position('-', pool_name, 1) = 0 then pool_name else
substr(pool_name, 1, position('-', pool_name, 1) - 1) end as pool_name, pool_depth, row_number() over (order by pool_depth desc) as pool_depth_rank,
number_of_addresses, row_number() over (order by number_of_addresses desc) as number_of_addresses_rank,
pool_depth_rank + number_of_addresses_rank as total_rank from final_table
order by total_rank asc
Run a query to Download Data