DATE | DAU | NEW_ACCOUNT | CUM_ACCOUNT | RETURNING_ACCOUNT | NEW_ACCOUNT_PERCENT | RETURNING_ACCOUNT_PERCENT | daily avg DAU | AVARAGE_7_DAU | daily avg new account | AVARAGE_7_NEW_ACCOUNT | |
|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2025-04-30 00:00:00.000 | 1622 | 1163 | 30759 | 459 | 71.701603 | 28.298397 | 1249.294118 | 2761.375 | 904.676471 | 2138.75 |
2 | 2025-04-29 00:00:00.000 | 6175 | 5799 | 29596 | 376 | 93.910931 | 6.089069 | 1238 | 2752.875 | 896.848485 | 2097.5 |
3 | 2025-04-28 00:00:00.000 | 1579 | 856 | 23797 | 723 | 54.211526 | 45.788474 | 1083.71875 | 2167.625 | 743.65625 | 1504.75 |
4 | 2025-04-27 00:00:00.000 | 6225 | 5680 | 22941 | 545 | 91.24498 | 8.75502 | 1067.741935 | 2104.625 | 740.032258 | 1486.5 |
5 | 2025-04-26 00:00:00.000 | 1529 | 1096 | 17261 | 433 | 71.680837 | 28.319163 | 895.833333 | 1544.875 | 575.366667 | 894 |
6 | 2025-04-25 00:00:00.000 | 2325 | 1347 | 16165 | 978 | 57.935484 | 42.064516 | 874 | 1495 | 557.413793 | 845.75 |
7 | 2025-04-24 00:00:00.000 | 1333 | 518 | 14818 | 815 | 38.859715 | 61.140285 | 822.178571 | 1364.25 | 529.214286 | 803.625 |
8 | 2025-04-23 00:00:00.000 | 1303 | 651 | 14300 | 652 | 49.961627 | 50.038373 | 803.259259 | 1321.125 | 529.62963 | 814.75 |
9 | 2025-04-22 00:00:00.000 | 1554 | 833 | 13649 | 721 | 53.603604 | 46.396396 | 784.038462 | 1293.375 | 524.961538 | 806.125 |
10 | 2025-04-21 00:00:00.000 | 1493 | 1057 | 12816 | 436 | 70.797053 | 29.202947 | 753.24 | 1196.25 | 512.64 | 768.25 |
11 | 2025-04-20 00:00:00.000 | 1075 | 710 | 11759 | 365 | 66.046512 | 33.953488 | 722.416667 | 1098.375 | 489.958333 | 690 |
12 | 2025-04-19 00:00:00.000 | 1747 | 940 | 11049 | 807 | 53.806525 | 46.193475 | 707.086957 | 1089.75 | 480.391304 | 688.75 |
13 | 2025-04-18 00:00:00.000 | 1130 | 710 | 10109 | 420 | 62.831858 | 37.168142 | 659.818182 | 985.5 | 459.5 | 638.875 |
14 | 2025-04-17 00:00:00.000 | 1279 | 1010 | 9399 | 269 | 78.967944 | 21.032056 | 637.428571 | 958.75 | 447.571429 | 621.25 |
15 | 2025-04-16 00:00:00.000 | 988 | 607 | 8389 | 381 | 61.437247 | 38.562753 | 605.35 | 931.75 | 419.45 | 565.125 |
16 | 2025-04-15 00:00:00.000 | 1081 | 582 | 7782 | 499 | 53.839038 | 46.160962 | 585.210526 | 942 | 409.578947 | 562.5 |
17 | 2025-04-14 00:00:00.000 | 777 | 530 | 7200 | 247 | 68.211068 | 31.788932 | 557.666667 | 881.125 | 400 | 553 |
18 | 2025-04-13 00:00:00.000 | 710 | 431 | 6670 | 279 | 60.704225 | 39.295775 | 544.764706 | 841.5 | 392.352941 | 538.625 |
19 | 2025-04-12 00:00:00.000 | 1006 | 700 | 6239 | 306 | 69.582505 | 30.417495 | 534.4375 | 820.625 | 389.9375 | 547.375 |
20 | 2025-04-11 00:00:00.000 | 913 | 541 | 5539 | 372 | 59.255203 | 40.744797 | 503 | 764.875 | 369.266667 | 523.25 |
BlockTrackernew vs returning users
Updated 2025-04-30
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 DAU_u as (
SELECT
date_trunc('{{granularity}}', block_timestamp) as date,
count(DISTINCT from_address) as DAU
FROM mezo.testnet.fact_transactions
GROUP BY date
)
,new as (
SELECT
date_trunc('{{granularity}}', first_tx) as date,
count(DISTINCT user) as new_user
FROM (
SELECT
from_address as user,
min(block_timestamp) as first_tx
FROM mezo.testnet.fact_transactions
GROUP BY 1)
GROUP BY 1)
SELECT
a.date,
Dau,
coalesce(new_user,0) as new_account,
sum(new_account) over (ORDER by date) as cum_account,
Dau - new_account as returning_account,
100 * new_account / Dau as new_account_percent,
100 * returning_account / Dau as returning_account_percent,
avg(Dau)over(ORDER BY date) as "daily avg DAU",
AVG(Dau) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avarage_7_Dau,
avg(new_account)over(ORDER BY date) as "daily avg new account",
AVG(new_account) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avarage_7_new_account
FROM DAU_u a
LEFT JOIN new b using(date)
ORDER BY 1 DESC
Last run: 18 days ago
34
3KB
4s