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.000 | AVAX.AVAX | 547.867188 | 733.708447454545 | 0 | 0 | |||||||
2 | 2025-05-24 00:00:00.000 | STABLECOIN | 559.909292 | 733.708447454545 | 0 | 0 | |||||||
3 | 2025-05-24 00:00:00.000 | GAIA.ATOM | 715.879781 | 733.708447454545 | 0 | 0 | |||||||
4 | 2025-05-24 00:00:00.000 | BSC.BNB | 416.071429 | 733.708447454545 | 0 | 0 | |||||||
5 | 2025-05-24 00:00:00.000 | Others | 946.139404 | 733.708447454545 | 0 | 0 | |||||||
6 | 2025-05-24 00:00:00.000 | BASE.ETH | 119.0625 | 733.708447454545 | 0 | 0 | |||||||
7 | 2025-05-24 00:00:00.000 | ETH.ETH | 959.651131 | 733.708447454545 | 0 | 0 | |||||||
8 | 2025-05-24 00:00:00.000 | BTC.BTC | 981.853641 | 733.708447454545 | 0 | 0 | |||||||
9 | 2025-05-24 00:00:00.000 | BCH.BCH | 983.372449 | 733.708447454545 | 0 | 0 | |||||||
10 | 2025-05-24 00:00:00.000 | LTC.LTC | 996.731107 | 733.708447454545 | 0 | 0 | |||||||
11 | 2025-05-24 00:00:00.000 | DOGE.DOGE | 844.255 | 733.708447454545 | 0 | 0 | |||||||
12 | 2025-05-23 00:00:00.000 | STABLECOIN | 559.750276 | 733.23338 | 0 | 0 | |||||||
13 | 2025-05-23 00:00:00.000 | AVAX.AVAX | 546.867188 | 733.23338 | 0 | 0 | |||||||
14 | 2025-05-23 00:00:00.000 | BTC.BTC | 980.853641 | 733.23338 | 0 | 0 | |||||||
15 | 2025-05-23 00:00:00.000 | DOGE.DOGE | 843.255 | 733.23338 | 0 | 0 | |||||||
16 | 2025-05-23 00:00:00.000 | BSC.BNB | 415.071429 | 733.23338 | 0 | 0 | |||||||
17 | 2025-05-23 00:00:00.000 | ETH.ETH | 958.651131 | 733.23338 | 0 | 0 | |||||||
18 | 2025-05-23 00:00:00.000 | BASE.ETH | 118.0625 | 733.23338 | 0 | 0 | |||||||
19 | 2025-05-23 00:00:00.000 | GAIA.ATOM | 714.879781 | 733.23338 | 0 | 0 | |||||||
20 | 2025-05-23 00:00:00.000 | LTC.LTC | 995.731107 | 733.23338 | 0 | 0 |
pietrektUnrealized P/L for LPs
Updated 47 minutes ago
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 1 hour agoAuto-refreshes every 24 hours
...
13742
3MB
30s