ACTIVITY_GROUP | NUM_USERS | AVG_DAILY_VOLUME_PER_USER | TOTAL_VOLUME_IN_GROUP | |
|---|---|---|---|---|
1 | A. 1 day | 1340330 | 293.26 | 393060392.94 |
2 | B. 2-5 days | 533307 | 161.55 | 209057759.1 |
3 | C. 6-10 days | 60499 | 123.55 | 56424673.86 |
4 | D. 11-20 days | 35495 | 136.57 | 78404262.92 |
5 | E. 20-30 days | 182776 | 86.49 | 443932913.02 |
aureasarsanedesevolution vol 3 .2
Updated 6 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 'A. 1 day'
WHEN active_days BETWEEN 2 AND 5 THEN 'B. 2-5 days'
WHEN active_days BETWEEN 6 AND 10 THEN 'C. 6-10 days'
WHEN active_days BETWEEN 10 AND 20 THEN 'D. 11-20 days'
else 'E. 20-30 days'
END AS activity_group,
from_address,
total_apt_amount_usd,
Last run: 6 days ago
5
213B
9s