ACTIVITY_GROUP | VOLUME_GROUP | NUM_USERS | AVG_DAILY_VOLUME_PER_USER | TOTAL_VOLUME_IN_GROUP | |
|---|---|---|---|---|---|
1 | 1 day | High volume (>1000 USD) | 5578 | 69326.7 | 386704349.05 |
2 | 1 day | Low volume (<100 USD) | 1317548 | 1.37 | 1806342.53 |
3 | 1 day | Medium volume (100-1000 USD) | 17204 | 264.46 | 4549701.36 |
4 | 2-5 days | High volume (>1000 USD) | 2287 | 37407.43 | 207462656.32 |
5 | 2-5 days | Low volume (<100 USD) | 526709 | 0.2 | 263041.34 |
6 | 2-5 days | Medium volume (100-1000 USD) | 4311 | 115.31 | 1332061.44 |
7 | 6-10 days | High volume (>1000 USD) | 531 | 14044.69 | 56307940.92 |
8 | 6-10 days | Low volume (<100 USD) | 59684 | 0.04 | 13530.1 |
9 | 6-10 days | Medium volume (100-1000 USD) | 284 | 52.59 | 103202.84 |
10 | More than 10 days | High volume (>1000 USD) | 587 | 35183.65 | 522272314.32 |
11 | More than 10 days | Low volume (<100 USD) | 217551 | 0 | 12575.28 |
12 | More than 10 days | Medium volume (100-1000 USD) | 133 | 23.97 | 52286.34 |
aureasarsanedesevolution vol 3
Updated 5 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_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: 5 days ago
12
774B
9s