jackiehighnoonwallet score
Updated 2024-02-26
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
›
⌄
-- rank each wallet on the below criteria and output a 'score'
-- 1. percent of trades with buy/sell interval > 5
-- 2. average profit of trades in above interval
-- 3. percentage of winning trades in above interval
-- 4. average number of sells per token
WITH TOT_SCORE AS(
WITH SCORE AS(
WITH BUY AS (
WITH DATES AS (
WITH BUY_TRANS AS(
SELECT
DATE_TRUNC('minute', BLOCK_TIMESTAMP) as BUY_DATE,
SWAP_TO_MINT as TOKEN,
SWAPPER
FROM
solana.defi.fact_swaps
WHERE
SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' --ONLY SOL TO SPL
AND SWAPPER = '{{wallet}}' --WALLET DOING SWAPPING
--AND SWAP_FROM_MINT = 'Hz9KLFv7MDmaMd3D77CFCvDpGT3A7qDkEKER84DW6jei' --SPECIFIC COIN
GROUP BY
TOKEN,
BUY_DATE,
SWAPPER
ORDER BY
BUY_DATE DESC
)
SELECT
TOKEN,
SWAPPER,
MIN(BUY_DATE) AS FIRST_BUY_DATE,
MAX(BUY_DATE) AS LAST_BUY_DATE,
COUNT(*) AS TOTAL_BUYS
FROM
BUY_TRANS
QueryRunArchived: QueryRun has been archived