andreafiandroUnrealized profit
Updated 2024-08-06
99
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
›
⌄
SELECT
swapper,
sum(sol_bought) as sol_bought,
sum(sol_sold) as sol_sold,
sum(sol_sold) - sum(sol_bought) as pnl,
max(block_timestamp) as last_transaction,
case when sum(sol_bought) > 0 then (sum(sol_sold) - sum(sol_bought)) / sum(sol_bought) else 0 end as pnl_perc,
sum(sol_amount) as sol_amount, sum(token_amount) as token_amount, count(*) as n_trades, sum(buy) as tot_buy, sum(sell) as tot_sell
FROM
(
SELECT
block_timestamp,
inserted_timestamp,
modified_timestamp,
block_id,
swapper,
CASE
WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN 'buy'
ELSE 'sell'
END AS side,
CASE
WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN SWAP_TO_MINT
ELSE SWAP_FROM_MINT
END AS token,
CASE
WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN 1
ELSE 0
END AS buy,
CASE
WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN 0
ELSE 1
END AS sell,
CASE
WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN - SWAP_FROM_AMOUNT
ELSE SWAP_TO_AMOUNT
END AS sol_amount,
QueryRunArchived: QueryRun has been archived