ACTIVITY_GROUP | VOLUME_GROUP | NUM_USERS | AVG_DAILY_VOLUME_PER_USER | TOTAL_VOLUME_IN_GROUP | |
|---|---|---|---|---|---|
1 | 1 day | High volume (>1000 USD) | 5653 | 87950.96 | 497186796.03 |
2 | 1 day | Low volume (<100 USD) | 1337135 | 1.4 | 1871286.97 |
3 | 1 day | Medium volume (100-1000 USD) | 16209 | 272.84 | 4422474.92 |
4 | 2-5 days | High volume (>1000 USD) | 9049 | 7463.97 | 171447524.59 |
5 | 2-5 days | Low volume (<100 USD) | 640252 | 0.46 | 777709.5 |
6 | 2-5 days | Medium volume (100-1000 USD) | 3954 | 138.6 | 1354343.11 |
7 | 6-10 days | High volume (>1000 USD) | 1160 | 15729.71 | 138333366.67 |
8 | 6-10 days | Low volume (<100 USD) | 70218 | 0.1 | 47456.94 |
9 | 6-10 days | Medium volume (100-1000 USD) | 1228 | 33.69 | 316983.74 |
10 | More than 10 days | High volume (>1000 USD) | 632 | 37553.65 | 574237119.98 |
11 | More than 10 days | Low volume (<100 USD) | 218664 | 0 | 14605.99 |
12 | More than 10 days | Medium volume (100-1000 USD) | 867 | 42.05 | 440213.8 |
aureasarsanedesevolution vol 3
Updated 15 minutes 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_data AS (
SELECT
symbol,
token_address,
decimals,
AVG(price) AS token_price,
DATE_TRUNC('day', hour) AS price_date
FROM aptos.price.ez_prices_hourly
GROUP BY symbol, token_address, decimals, DATE_TRUNC('day', hour)
),
users AS (
SELECT
DISTINCT from_address,
n.token_address,
MIN(TRUNC(block_timestamp, 'day')) AS first_transfer,
COUNT(DISTINCT TRUNC(block_timestamp, 'day')) AS active_days,
SUM(amount * (token_price) / POW(10, decimals)) AS total_apt_amount_usd,
AVG(amount * (token_price) / POW(10, decimals)) AS avg_apt_amount_usd
FROM aptos.core.ez_native_transfers n
LEFT JOIN price_data p
ON n.token_address = p.token_address
AND TRUNC(block_timestamp, 'day') = price_date
where TRUNC(block_timestamp, 'day')>=current_date-INTERVAL '{{days}} days' and TRUNC(block_timestamp, 'day')<current_date
GROUP BY from_address, n.token_address
),
user_analysis AS (
SELECT
CASE
WHEN active_days = 1 THEN '1 day'
WHEN active_days BETWEEN 2 AND 5 THEN '2-5 days'
WHEN active_days BETWEEN 6 AND 10 THEN '6-10 days'
WHEN active_days > 10 THEN 'More than 10 days'
END AS activity_group,
CASE
WHEN total_apt_amount_usd < 100 THEN 'Low volume (<100 USD)'
WHEN total_apt_amount_usd BETWEEN 100 AND 1000 THEN 'Medium volume (100-1000 USD)'
Last run: 15 minutes ago
12
775B
17s