datavortexwhat are traders trading
Updated 2025-02-18
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 swap_data AS (
SELECT
swap_from_mint,
swap_to_mint,
swap_from_symbol,
swap_to_symbol,
tx_id,
swapper,
swap_from_amount_usd,
swap_to_amount_usd,
block_timestamp,
CASE
WHEN SUM(swap_from_amount_usd) <= 1000 THEN 'Small Traders'
WHEN SUM(swap_from_amount_usd) <= 10000 THEN 'Medium Traders'
WHEN SUM(swap_from_amount_usd) > 10000 THEN 'Whales'
ELSE 'Unknown'
END AS trader_category
FROM
solana.marinade.ez_swaps
WHERE
swap_from_symbol = 'MSOL'
AND block_timestamp >= CURRENT_DATE - INTERVAL '1 MONTH'
GROUP BY
swap_from_mint, swap_to_mint, swap_from_symbol, swap_to_symbol, tx_id, swapper, swap_from_amount_usd, swap_to_amount_usd, block_timestamp
),
ranked_swap_pairs AS (
SELECT
trader_category,
swap_from_symbol || '-' || swap_to_symbol AS swap_pair,
COUNT(DISTINCT tx_id) AS swap_count,
SUM(swap_from_amount_usd) AS total_swap_volume_usd,
ROW_NUMBER() OVER (PARTITION BY trader_category ORDER BY SUM(swap_from_amount_usd) DESC) AS rank
FROM
swap_data
GROUP BY
trader_category, swap_from_symbol, swap_to_symbol