MetiocreThorchain: Weighted average of liquidity
Updated 2022-05-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
31
32
33
34
35
36
›
⌄
with add_liq as (
SELECT pool_name, sum(datediff(day, to_timestamp('2021-01-01'), block_timestamp) * (rune_amount_usd + asset_amount_usd)) as amount,
amount/sum(rune_amount_usd + asset_amount_usd) as weighed_avg
FROM thorchain.liquidity_actions
WHERE lp_action = 'add_liquidity' AND (rune_amount_usd + asset_amount_usd >0)
group by 1
),
remove_liq as (
SELECT pool_name, sum(datediff(day, to_timestamp('2021-01-01'), block_timestamp) * (rune_amount_usd + asset_amount_usd)) as amount,
amount/sum(rune_amount_usd + asset_amount_usd) as weighed_avg
FROM thorchain.liquidity_actions
WHERE lp_action = 'remove_liquidity' AND (rune_amount_usd + asset_amount_usd >0)
group by 1
)
SELECT A.pool_name, R.weighed_avg - A.weighed_avg as weighted_avg
FROM add_liq AS A, remove_liq AS R
where A.pool_name = R.pool_name
-- SELECT pool_name, sum(datediff(day, to_timestamp('2022-01-01'), block_timestamp) * (rune_amount_usd + asset_amount_usd)) as amount,
-- amount/sum(rune_amount_usd + asset_amount_usd) as weighed_avg
-- FROM thorchain.liquidity_actions
-- WHERE block_timestamp::date >= '2022-01-01' AND lp_action = 'remove_liquidity'
-- select * from thorchain.liquidity_actions
-- order by block_timestamp asc limit 10
-- -- select * from thorchain.liquidity_actions
-- -- where lp_action='remove_liquidity' limit 10
-- select * from thorchain.liquidity_actions
-- where stake_units = '29805395181'
Run a query to Download Data