pietrektUnrealized P/L for LPs
    Updated 47 minutes ago
    WITH deposit_units 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, asset_amount, rune_amount, 0 as basis_points, 1 as type
    FROM thorchain.defi.fact_liquidity_actions
    WHERE lp_action = 'add_liquidity' and pool_name like '%.%'
    ),
    withdraw_units 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 rune_amount, -asset_amount as asset_amount, -stake_units as stake_units, UNSTAKE_BASIS_POINTS as basis_points, 0 as type
    FROM thorchain.defi.fact_liquidity_actions WHERE lp_action = 'remove_liquidity' and pool_name like '%.%'
    ),
    unioned_units AS (
    SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, rune_amount, basis_points, type FROM deposit_units
    UNION
    SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, rune_amount, basis_points, type FROM withdraw_units
    ),

    block_prices AS (SELECT to_date(block_timestamp) as day, pool_name, avg(price_asset_rune) as price_asset_rune,
    avg(asset_usd) as asset_usd from thorchain.price.fact_prices GROUP BY day, pool_name),

    asym_deposits_corrected AS (SELECT a.block_timestamp, tx_id, address, a.pool_name, stake_units, basis_points, type, price_asset_rune,
    Last run: about 1 hour agoAuto-refreshes every 24 hours
    DAY
    ASSET_NAME
    AVG_LP_POOL_AGE
    AVG_LP_AGE
    AVG_RUNE_PRICE
    UNREALIZED_PL_USD
    UNREALIZED_PL_RUNE
    TOTAL_UNREALIZED_PL_USD
    TOTAL_UNREALIZED_PL_RUNE
    ILP_EXPOSURE_USD
    ILP_EXPOSURE_RUNE
    TOTAL_ILP_EXPOSURE_USD
    TOTAL_ILP_EXPOSURE_RUNE
    1
    2025-05-24 00:00:00.000AVAX.AVAX547.867188733.70844745454500
    2
    2025-05-24 00:00:00.000STABLECOIN559.909292733.70844745454500
    3
    2025-05-24 00:00:00.000GAIA.ATOM715.879781733.70844745454500
    4
    2025-05-24 00:00:00.000BSC.BNB416.071429733.70844745454500
    5
    2025-05-24 00:00:00.000Others946.139404733.70844745454500
    6
    2025-05-24 00:00:00.000BASE.ETH119.0625733.70844745454500
    7
    2025-05-24 00:00:00.000ETH.ETH959.651131733.70844745454500
    8
    2025-05-24 00:00:00.000BTC.BTC981.853641733.70844745454500
    9
    2025-05-24 00:00:00.000BCH.BCH983.372449733.70844745454500
    10
    2025-05-24 00:00:00.000LTC.LTC996.731107733.70844745454500
    11
    2025-05-24 00:00:00.000DOGE.DOGE844.255733.70844745454500
    12
    2025-05-23 00:00:00.000STABLECOIN559.750276733.2333800
    13
    2025-05-23 00:00:00.000AVAX.AVAX546.867188733.2333800
    14
    2025-05-23 00:00:00.000BTC.BTC980.853641733.2333800
    15
    2025-05-23 00:00:00.000DOGE.DOGE843.255733.2333800
    16
    2025-05-23 00:00:00.000BSC.BNB415.071429733.2333800
    17
    2025-05-23 00:00:00.000ETH.ETH958.651131733.2333800
    18
    2025-05-23 00:00:00.000BASE.ETH118.0625733.2333800
    19
    2025-05-23 00:00:00.000GAIA.ATOM714.879781733.2333800
    20
    2025-05-23 00:00:00.000LTC.LTC995.731107733.2333800
    ...
    13742
    3MB
    30s