mondovholder categ after
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
›
⌄
WITH balances AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS date,
tx_to,
SUM(CASE WHEN tx_from = tx_to THEN amount ELSE 0 END) OVER (PARTITION BY tx_to ORDER BY DATE_TRUNC('day', block_timestamp)) AS received_balance,
SUM(CASE WHEN tx_from <> tx_to THEN amount ELSE 0 END) OVER (PARTITION BY tx_to ORDER BY DATE_TRUNC('day', block_timestamp)) AS sent_balance
FROM solana.core.fact_transfers
WHERE mint = 'So11111111111111111111111111111111111111112'
),
categories AS (
SELECT
-- date,
CASE
WHEN (received_balance - sent_balance) < 1 THEN 'Shrimp < 1 SOL'
WHEN (received_balance - sent_balance) >= 1 AND (received_balance - sent_balance) < 10 THEN 'Crab < 10 SOL'
WHEN (received_balance - sent_balance) >= 10 AND (received_balance - sent_balance) < 100 THEN 'Octopus < 100 SOL'
WHEN (received_balance - sent_balance) >= 100 AND (received_balance - sent_balance) < 1000 THEN 'Fish < 1,000 SOL'
WHEN (received_balance - sent_balance) >= 1000 AND (received_balance - sent_balance) < 10000 THEN 'Shark < 10,000 SOL'
WHEN (received_balance - sent_balance) >= 10000 THEN 'Whale > 10,000 SOL'
END AS category,
COUNT(tx_to) AS holders
FROM balances
WHERE date >= '2023-06-05'::date
AND date <= '2023-06-05'::date + INTERVAL '15 days'
GROUP BY category
)
SELECT holders, category
FROM categories
ORDER BY category;
Run a query to Download Data