flipside

    Weighted-Average LP Duration (Days)
    NatWeighted-Average LP Duration (Days)
    Updated 2022-04-29
    WITH add_liq as (
    SELECT
    block_timestamp AS date_added,
    CONCAT(pool_name,'-',from_address) as id_added
    FROM thorchain.liquidity_actions
    WHERE LP_ACTION LIKE '%add%'
    GROUP BY 1,2
    ORDER BY 1,2
    ),
    remove_liq as (
    SELECT
    block_timestamp AS date_removed,
    CONCAT(pool_name,'-',from_address) as id_removed
    FROM thorchain.liquidity_actions
    WHERE LP_ACTION LIKE '%remove%'
    GROUP BY 1,2
    ORDER BY 1,2
    ),
    dates as (
    SELECT
    add_liq.id_added as id,
    REGEXP_SUBSTR(id,'[A-Z]*.[A-Z]*') as pool,
    add_liq.date_added as date_added,
    remove_liq.date_removed as date_removed,
    DATEDIFF(hour, date_added, date_removed) as diff_date
    FROM add_liq
    INNER JOIN remove_liq
    ON add_liq.id_added=remove_liq.id_removed
    WHERE diff_date > 0
    ORDER BY 1,2,3
    )
    SELECT
    DISTINCT pool,
    REGEXP_SUBSTR(pool,'[A-Z]*') as main_pool_label,
    ROUND(AVG(diff_date)/24,3) as average_difference_days
    FROM dates
    Run a query to Download Data