binhachonWeighted-Average LP Duration
    Updated 2022-04-30
    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