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.000 | BASE.ETH | 143.058824 | 772.157941181818 | 0 | 0 | |||||||
2 | 2025-07-03 00:00:00.000 | GAIA.ATOM | 758.289694 | 772.157941181818 | 0 | 0 | |||||||
3 | 2025-07-03 00:00:00.000 | BCH.BCH | 1023.372449 | 772.157941181818 | 0 | 0 | |||||||
4 | 2025-07-03 00:00:00.000 | BTC.BTC | 1023.068474 | 772.157941181818 | 0 | 0 | |||||||
5 | 2025-07-03 00:00:00.000 | Others | 972.252695 | 772.157941181818 | 0 | 0 | |||||||
6 | 2025-07-03 00:00:00.000 | DOGE.DOGE | 885.329114 | 772.157941181818 | 0 | 0 | |||||||
7 | 2025-07-03 00:00:00.000 | BSC.BNB | 454.427711 | 772.157941181818 | 0 | 0 | |||||||
8 | 2025-07-03 00:00:00.000 | STABLECOIN | 606.577753 | 772.157941181818 | 0 | 0 | |||||||
9 | 2025-07-03 00:00:00.000 | ETH.ETH | 1000.440748 | 772.157941181818 | 0 | 0 | |||||||
10 | 2025-07-03 00:00:00.000 | LTC.LTC | 1039.262411 | 772.157941181818 | 0 | 0 | |||||||
11 | 2025-07-03 00:00:00.000 | AVAX.AVAX | 587.65748 | 772.157941181818 | 0 | 0 | |||||||
12 | 2025-07-02 00:00:00.000 | BTC.BTC | 1021.782531 | 770.979916545455 | 1.318 | -28507026.0576508 | -21629003.0786425 | -37277032.0248208 | -28283028.8503952 | 16121394.6978436 | 12231710.6963912 | 17461300.8101638 | 13248331.4189407 |
13 | 2025-07-02 00:00:00.000 | STABLECOIN | 605.577753 | 770.979916545455 | 1.318 | -935637.231226055 | -709891.677713243 | -37277032.0248208 | -28283028.8503952 | 30523.90960878 | 23159.26373959 | 17461300.8101638 | 13248331.4189407 |
14 | 2025-07-02 00:00:00.000 | BASE.ETH | 142.058824 | 770.979916545455 | 1.318 | 1485.346150688 | 1126.969765317 | -37277032.0248208 | -28283028.8503952 | 0 | 0 | 17461300.8101638 | 13248331.4189407 |
15 | 2025-07-02 00:00:00.000 | BSC.BNB | 453.427711 | 770.979916545455 | 1.318 | -1643826.41049558 | -1247212.754549 | -37277032.0248208 | -28283028.8503952 | 0 | 0 | 17461300.8101638 | 13248331.4189407 |
16 | 2025-07-02 00:00:00.000 | DOGE.DOGE | 882.583333 | 770.979916545455 | 1.318 | -948636.324763183 | -719754.419395434 | -37277032.0248208 | -28283028.8503952 | 82267.849195251 | 62418.701969082 | 17461300.8101638 | 13248331.4189407 |
17 | 2025-07-02 00:00:00.000 | GAIA.ATOM | 757.289694 | 770.979916545455 | 1.318 | 216954.032036075 | 164608.522030406 | -37277032.0248208 | -28283028.8503952 | 0 | 0 | 17461300.8101638 | 13248331.4189407 |
18 | 2025-07-02 00:00:00.000 | Others | 971.326148 | 770.979916545455 | 1.318 | 1076172.03150331 | 816518.992035894 | -37277032.0248208 | -28283028.8503952 | 25574.088110281 | 19403.708733142 | 17461300.8101638 | 13248331.4189407 |
19 | 2025-07-02 00:00:00.000 | ETH.ETH | 999.440748 | 770.979916545455 | 1.318 | -2795397.5315943 | -2120938.94658141 | -37277032.0248208 | -28283028.8503952 | 467487.391196394 | 354694.530498023 | 17461300.8101638 | 13248331.4189407 |
20 | 2025-07-02 00:00:00.000 | BCH.BCH | 1022.372449 | 770.979916545455 | 1.318 | -3395199.21261976 | -2576023.68180558 | -37277032.0248208 | -28283028.8503952 | 595812.766646114 | 452058.244799783 | 17461300.8101638 | 13248331.4189407 |
pietrektUnrealized P/L for LPs
Updated 6 hours agoCopy Reference Fork
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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
14182
3MB
392s