pietrektUnrealized P/L for LPs
    Updated 6 hours 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 6 hours 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-07-03 00:00:00.000BASE.ETH143.058824772.15794118181800
    2
    2025-07-03 00:00:00.000GAIA.ATOM758.289694772.15794118181800
    3
    2025-07-03 00:00:00.000BCH.BCH1023.372449772.15794118181800
    4
    2025-07-03 00:00:00.000BTC.BTC1023.068474772.15794118181800
    5
    2025-07-03 00:00:00.000Others972.252695772.15794118181800
    6
    2025-07-03 00:00:00.000DOGE.DOGE885.329114772.15794118181800
    7
    2025-07-03 00:00:00.000BSC.BNB454.427711772.15794118181800
    8
    2025-07-03 00:00:00.000STABLECOIN606.577753772.15794118181800
    9
    2025-07-03 00:00:00.000ETH.ETH1000.440748772.15794118181800
    10
    2025-07-03 00:00:00.000LTC.LTC1039.262411772.15794118181800
    11
    2025-07-03 00:00:00.000AVAX.AVAX587.65748772.15794118181800
    12
    2025-07-02 00:00:00.000BTC.BTC1021.782531770.9799165454551.318-28507026.0576508-21629003.0786425-37277032.0248208-28283028.850395216121394.697843612231710.696391217461300.810163813248331.4189407
    13
    2025-07-02 00:00:00.000STABLECOIN605.577753770.9799165454551.318-935637.231226055-709891.677713243-37277032.0248208-28283028.850395230523.9096087823159.2637395917461300.810163813248331.4189407
    14
    2025-07-02 00:00:00.000BASE.ETH142.058824770.9799165454551.3181485.3461506881126.969765317-37277032.0248208-28283028.85039520017461300.810163813248331.4189407
    15
    2025-07-02 00:00:00.000BSC.BNB453.427711770.9799165454551.318-1643826.41049558-1247212.754549-37277032.0248208-28283028.85039520017461300.810163813248331.4189407
    16
    2025-07-02 00:00:00.000DOGE.DOGE882.583333770.9799165454551.318-948636.324763183-719754.419395434-37277032.0248208-28283028.850395282267.84919525162418.70196908217461300.810163813248331.4189407
    17
    2025-07-02 00:00:00.000GAIA.ATOM757.289694770.9799165454551.318216954.032036075164608.522030406-37277032.0248208-28283028.85039520017461300.810163813248331.4189407
    18
    2025-07-02 00:00:00.000Others971.326148770.9799165454551.3181076172.03150331816518.992035894-37277032.0248208-28283028.850395225574.08811028119403.70873314217461300.810163813248331.4189407
    19
    2025-07-02 00:00:00.000ETH.ETH999.440748770.9799165454551.318-2795397.5315943-2120938.94658141-37277032.0248208-28283028.8503952467487.391196394354694.53049802317461300.810163813248331.4189407
    20
    2025-07-02 00:00:00.000BCH.BCH1022.372449770.9799165454551.318-3395199.21261976-2576023.68180558-37277032.0248208-28283028.8503952595812.766646114452058.24479978317461300.810163813248331.4189407
    ...
    14182
    3MB
    392s