seoulprotocolBonk Paperhands
Updated 2024-10-08
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
›
⌄
WITH largest_sells AS (
-- Step 1: Find the largest sell for each owner
SELECT
OWNER,
MIN(BALANCE - PRE_BALANCE) AS LARGEST_SELL -- Find the largest sell (most negative change)
FROM
solana.core.fact_token_balances tb
WHERE
tb.mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' -- Filter by BONK token
AND tb.block_timestamp BETWEEN '2022-01-01 00:00:00' AND '2023-11-01 00:00:00' -- Apply date filter
AND tb.SUCCEEDED = TRUE
AND (BALANCE - PRE_BALANCE) < 0 -- Filter for negative balance changes (sells)
GROUP BY
OWNER
),
remaining_balances AS (
-- Step 2: Calculate the remaining balance for each owner (net change in balance over time)
SELECT
OWNER,
SUM(BALANCE - PRE_BALANCE) AS REMAINING_BALANCE -- Sum the net change in balance
FROM
solana.core.fact_token_balances tb
WHERE
tb.mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' -- Filter by BONK token
AND tb.block_timestamp BETWEEN '2022-01-01 00:00:00' AND '2023-11-01 00:00:00' -- Apply date filter
AND tb.SUCCEEDED = TRUE
GROUP BY
OWNER
)
-- Step 3: Combine the largest sell and remaining balance grouped by owner, including label information and calculating the weight
SELECT
ls.OWNER,
ls.LARGEST_SELL,
rb.REMAINING_BALANCE, -- Include the remaining balance at the end of the period (superior to -1)
(- ls.LARGEST_SELL - rb.REMAINING_BALANCE) AS WEIGHT, -- Corrected weight calculation
QueryRunArchived: QueryRun has been archived