binhachonWeighted-Average LP Duration
Updated 2022-04-30
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 day_list as (
select
distinct day
from thorchain.daily_pool_stats
--get day from this table to remove the period when thorchain shutdown the blockchain
),
user_first_lp_actions as (
select
distinct
date_trunc('day', block_timestamp) as first_time,
pool_name,
coalesce(from_address, asset_address) as address
from thorchain.liquidity_actions
where lp_action = 'add_liquidity'
qualify row_number() over (partition by address, pool_name order by first_time) = 1
),
user_list_with_day as (
select
day,
pool_name,
address
from day_list
left join user_first_lp_actions on (day >= first_time)
),
user_daily_changes as (
select
date_trunc('day', block_timestamp) as time,
pool_name,
coalesce(from_address, asset_address) as address,
sum(case when lp_action = 'add_liquidity' then stake_units else - stake_units end) as daily_stake_units
from thorchain.liquidity_actions
group by 1, 2, 3
),
user_list_with_day_and_stake_units as (
select
user_list_with_day.*,
Run a query to Download Data