WALLET_SEGMENT | WALLET_COUNT | TOTAL_VALUE_TRANSACTED | AVG_TRANSACTIONS_PER_WALLET | |
|---|---|---|---|---|
1 | Whale ($1M+) | 438 | 4361122215.13798 | 13872.390411 |
2 | Shark ($10K-$999K) | 20829 | 1535872319.07627 | 668.912286 |
3 | Dolphin ($1K-$9.9K) | 39420 | 124903817.880181 | 554.311314 |
4 | Crab (<$1K) | 293391 | 21485602.4346522 | 617.334489 |
m3jiWallet Behavior Segmentation
Updated 8 days ago
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 price AS (
select
date_trunc('day', HOUR) AS day,
TOKEN_ADDRESS,
avg(PRICE) as usd_price
from
ink.price.ez_prices_hourly
group by
1,
2
),
wallet_activity AS (
SELECT
FROM_ADDRESS AS wallet_address,
SUM(VALUE * p.usd_price) AS total_value,
COUNT(*) AS total_transactions
FROM
ink.core.fact_transactions tx
JOIN price p on date_trunc('day', block_timestamp) = p.day
WHERE
TX_SUCCEEDED = TRUE
GROUP BY
FROM_ADDRESS
)
SELECT
CASE
WHEN total_value < 1000 THEN 'Crab (<$1K)'
WHEN total_value BETWEEN 1000 AND 9999 THEN 'Dolphin ($1K-$9.9K)'
WHEN total_value BETWEEN 10000 AND 999999 THEN 'Shark ($10K-$999K)'
ELSE 'Whale ($1M+)'
END AS wallet_segment,
COUNT(wallet_address) AS wallet_count,
SUM(total_value) AS total_value_transacted,
AVG(total_transactions) AS avg_transactions_per_wallet
FROM
Last run: 8 days agoAuto-refreshes every 24 hours
4
218B
125s