SalehTHORChain LP Status_date
Updated 2024-11-08
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
›
⌄
--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