DAY | SYMBOL | NEW_USERS | UNIQUE_USERS | AVG_AMOUNT_PER_USER | |
|---|---|---|---|---|---|
1 | 2025-04-21 00:00:00.000 | APT | 82521 | 1405583 | 6.999239908 |
2 | 2025-04-20 00:00:00.000 | APT | 16867 | 1323062 | 18.418273174 |
3 | 2025-04-19 00:00:00.000 | APT | 11242 | 1306195 | 140.551318667 |
4 | 2025-04-18 00:00:00.000 | APT | 2234 | 1294953 | 398.993103085 |
5 | 2025-04-17 00:00:00.000 | APT | 6326 | 1292719 | 838.89262093 |
6 | 2025-04-16 00:00:00.000 | APT | 43413 | 1286393 | 18.324549658 |
7 | 2025-04-15 00:00:00.000 | APT | 59219 | 1242980 | 50.537994159 |
8 | 2025-04-14 00:00:00.000 | APT | 60325 | 1183761 | 20.823666038 |
9 | 2025-04-13 00:00:00.000 | APT | 54664 | 1123436 | 24.484133455 |
10 | 2025-04-12 00:00:00.000 | APT | 49035 | 1068772 | 92.568431771 |
11 | 2025-04-11 00:00:00.000 | APT | 51499 | 1019737 | 2003.424553539 |
12 | 2025-04-10 00:00:00.000 | APT | 48427 | 968238 | 15.775398611 |
13 | 2025-04-09 00:00:00.000 | APT | 50088 | 919811 | 35.525108201 |
14 | 2025-04-08 00:00:00.000 | APT | 48758 | 869723 | 30.189646201 |
15 | 2025-04-07 00:00:00.000 | APT | 51664 | 820965 | 634.679979079 |
16 | 2025-04-06 00:00:00.000 | APT | 51351 | 769301 | 225.83316329 |
17 | 2025-04-05 00:00:00.000 | APT | 46658 | 717950 | 125.083553063 |
18 | 2025-04-04 00:00:00.000 | APT | 50576 | 671292 | 238.513589892 |
19 | 2025-04-03 00:00:00.000 | APT | 51612 | 620716 | 25.784501699 |
20 | 2025-04-02 00:00:00.000 | APT | 54973 | 569104 | 94.39442311 |
aureasarsanedesevolution vol 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
›
⌄
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, token_address,
min(trunc(block_timestamp,'day')) as first_transfer, count(distinct trunc(block_timestamp,'day')) as active_days, sum(amount) as apt_amount,
avg(amount) as avg_apt_amount
from aptos.core.ez_native_transfers n group by 1,2
)
SELECT
DATE_TRUNC('day', first_transfer) AS day, symbol,
count(distinct from_address) as new_users,
sum(new_users) over (order by day) as unique_users,
avg(apt_amount*(token_price)/pow(10,decimals)) as avg_amount_per_user
FROM users n
LEFT JOIN price_data p
ON n.TOKEN_ADDRESS = p.token_address
AND DATE_TRUNC('day', n.first_transfer) = p.price_date
WHERE n.first_transfer >= DATE_TRUNC('day', CURRENT_DATE - {{days}}) and n.first_transfer < DATE_TRUNC('day', CURRENT_DATE)
GROUP BY day, symbol
ORDER BY day DESC
Last run: 6 days ago
30
2KB
12s