johnsmithbobQuery1
Updated 2025-03-16
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
›
⌄
--Could adjust sell usd to accounts for unsold tokens using sell_tokens_per_dollar
--This is DCA in and out, realized pnl only
--any tokens that never had a sell are considered valued at Zero
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-03-01' AND (swap_to_mint LIKE '%pump' )
--AND swapper = 'BRcR9czUxiANsXLZpgvYCWK9MFMeruj26FnnJyQrWXEr'
--WHERE block_id >= 318886493 AND block_id < 319327746 AND (swap_to_mint = 'G7BdNcDgVbadX5jLHyN5qE3bgXzQmAdYg2bRFA5bpump' )
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-03-01' AND (swap_from_mint LIKE '%pump' )
--AND swapper = 'BRcR9czUxiANsXLZpgvYCWK9MFMeruj26FnnJyQrWXEr'
--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,COALESCE(most_recent_sell,most_recent_buy)) as most_recent_swap,
COALESCE(sell_tokens_per_dollar,buy_tokens_per_dollar * 100) sell_tokens_per_dollar,
COALESCE(s.sell_q, b.buy_q) as sell_q, COALESCE(s.sell_usd,0) as sell_usd, COALESCE(s.sell_usd,0) - b.buy_usd as profit,
CASE WHEN COALESCE(s.sell_tokens_per_dollar,0) = 0 THEN .00001
ELSE buy_tokens_per_dollar / s.sell_tokens_per_dollar END as pct_change
FROM buys b
LEFT OUTER JOIN sells s ON s.swapper = b.swapper AND s.token = b.token
QueryRunArchived: QueryRun has been archived