fantaLP
Updated 2022-05-01
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 as
(
select pool_name,from_address,block_timestamp::date as date
from thorchain.liquidity_actions
where lp_action='add_liquidity'
)
, remove as
(
select pool_name,from_address,block_timestamp::date as date
from thorchain.liquidity_actions
where lp_action='remove_liquidity'
)
, lp as
(
select pool_name, (sum(case when lp_action = 'add_liquidity' then rune_amount_usd+asset_amount_usd end) - sum( case when lp_action = 'remove_liquidity' then rune_amount_usd+asset_amount_usd end) ) as LPsize
from
thorchain.liquidity_actions
group by pool_name
)
select
(
case
when lp.LPsize < 5000 then 'A.LPsize <5K : '
when lp.LPsize >= 5000 and lp.LPsize < 50000 then 'B. LPsize >= 5K and LPsize < 50K : '
when lp.LPsize >= 50000 and lp.LPsize < 500000 then 'C. LPsize >= 50K and LPsize < 500K : '
when lp.LPsize >= 500000 and lp.LPsize < 1000000 then 'D. LPsize >= 500K and lp.LPsize < 1M : '
when lp.LPsize >= 1000000 and lp.LPsize < 1500000 then 'E. LPsize >= 1M and LPsize < 1.5M : '
when lp.LPsize >= 1500000 and lp.LPsize < 2000000 then 'F. LPsize >= 1.5M and LPsize < 2M : '
when lp.LPsize >= 2000000 and lp.LPsize < 5000000 then 'G. LPsize >= 2M and LPsize < 5M : '
when lp.LPsize >= 5000000 and lp.LPsize < 10000000 then 'H. LPsize >= 5M and LPsize < 10M : '
when lp.LPsize >= 10000000 and lp.LPsize < 50000000 then 'I. LPsize >= 10M and LPsize < 50M : '
when lp.LPsize >= 50000000 then 'J. LPsize >= 50M : ' end
) as histogram , avg(datediff('hour',add.date,remove.date)) as Average_Hour
from add
inner join remove
Run a query to Download Data