johnsmithbobQuery1 copy
Updated 2025-02-27
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
›
⌄
-- forked from Query1 @ https://flipsidecrypto.xyz/studio/queries/fbd9064f-e19f-4504-9998-14e95b0003b8
--Could adjust sell usd to accounts for unsold tokens using sell_tokens_per_dollar
--This is DCA in and out, realized pnl only
WITH buys AS
(
SELECT swapper, swap_to_mint as token, SUM(swap_to_amount) as buy_q,SUM(swap_from_amount_usd) as buy_usd,
CASE WHEN SUM(swap_from_amount_usd) = 0 THEN 0 ELSE SUM(swap_to_amount) / SUM(swap_from_amount_usd)END as buy_tokens_per_dollar,
MAX(block_timestamp) as most_recent_buy, COUNT(*) as buy_count --SUM(swap_to_amount) / SUM(swap_from_amount_usd) as buy_price
FROM solana.defi.ez_dex_swaps s
WHERE block_timestamp >= '2025-02-01' AND (swap_to_mint LIKE '%pump' )
--WHERE block_id >= 318886493 AND block_id < 319327746 AND (swap_to_mint = 'G7BdNcDgVbadX5jLHyN5qE3bgXzQmAdYg2bRFA5bpump' )
AND swapper = 'DNfuF1L62WWyW3pNakVkyGGFzVVhj4Yr52jSmdTyeBHm'
GROUP BY swapper, swap_to_mint
),
sells AS
(
SELECT swapper, swap_from_mint as token, SUM(swap_from_amount) as sell_q,SUM(swap_to_amount_usd) as sell_usd,
CASE WHEN SUM(swap_to_amount_usd) = 0 THEN 0 ELSE SUM(swap_from_amount) / SUM(swap_to_amount_usd)END as sell_tokens_per_dollar,
MAX(block_timestamp) as most_recent_sell
FROM solana.defi.ez_dex_swaps s
WHERE block_timestamp >= '2025-02-01' AND (swap_from_mint LIKE '%pump' )
AND swapper = 'DNfuF1L62WWyW3pNakVkyGGFzVVhj4Yr52jSmdTyeBHm'
--WHERE block_id >= 318886493 AND block_id < 319327746 AND (swap_from_mint = 'G7BdNcDgVbadX5jLHyN5qE3bgXzQmAdYg2bRFA5bpump')
GROUP BY swapper, swap_from_mint
),
wallet_token_agg AS
(
select b.*,GREATEST(most_recent_buy,most_recent_sell) as most_recent_swap, sell_tokens_per_dollar, s.sell_q, s.sell_usd, s.sell_usd - b.buy_usd as profit, buy_tokens_per_dollar / sell_tokens_per_dollar as pct_change
FROM buys b
INNER JOIN sells s ON s.swapper = b.swapper AND s.token = b.token
WHERE b.buy_q >= s.sell_q
AND b.buy_tokens_per_dollar > 0 AND s.sell_tokens_per_dollar> 0
),
wallet_token_agg2 AS
(
QueryRunArchived: QueryRun has been archived