SalehTHORChain LP Status_date
    Updated 2024-11-08
    --Main Query forked from pietrekt / LP Realized P/L @ https://flipsidecrypto.xyz/pietrekt/q/XbV3v895M5vY/lp-realized-p-l

    WITH lp_deposits AS (
    SELECT block_timestamp,
    CASE
    WHEN tx_id is not null then tx_id
    ELSE asset_tx_id
    END as tx_id,
    CASE
    WHEN from_address is not null THEN from_address
    ELSE asset_address
    END as address, pool_name, stake_units,
    rune_amount as amount_change_rune, rune_amount as true_amount_rune, 0 as slip_fee_rune,
    asset_amount as amount_change_asset, asset_amount as true_amount_asset, 0 as slip_fee_asset, 1 as type
    FROM thorchain.defi.fact_liquidity_actions WHERE lp_action = 'add_liquidity' and pool_name like '%.%'),
    -- FULL LP WITHDRAW TRANSACTIONS
    lp_withdrawals AS (
    SELECT block_timestamp,
    CASE
    WHEN tx_id is not null then tx_id
    ELSE asset_tx_id
    END as tx_id,
    CASE
    WHEN from_address is not null THEN from_address
    ELSE asset_address
    END as address, pool_name, -rune_amount as amount_change_rune, -asset_amount as amount_change_asset, unstake_basis_points as basis_points, 0 as type
    FROM thorchain.defi.fact_liquidity_actions WHERE lp_action = 'remove_liquidity' and pool_name like '%.%'),

    lp_amount_withdrawn AS (SELECT a.block_timestamp, a.tx_id, address, a.pool_name, amount_change_rune, amount_change_asset,
    CASE
    WHEN b.asset = 'THOR.RUNE' THEN asset_e8 / pow(10, 8)
    ELSE 0
    END AS rune_amount,
    CASE
    WHEN b.asset <> 'THOR.RUNE' THEN asset_e8 / pow(10, 8)
    ELSE 0
    QueryRunArchived: QueryRun has been archived