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-06 00:00:00.000 | 53893 | 234 | 349251 | 53659 | 0.434194 | 99.565806 | 56688.352459 | 125965.625 | 2862.713115 | 683.75 |
2 | 2025-04-05 00:00:00.000 | 95546 | 650 | 349017 | 94896 | 0.680301 | 99.319699 | 56711.454545 | 137641.625 | 2884.438017 | 748 |
3 | 2025-04-04 00:00:00.000 | 155015 | 646 | 348367 | 154369 | 0.416734 | 99.583266 | 56387.833333 | 138459.875 | 2903.058333 | 777.75 |
4 | 2025-04-03 00:00:00.000 | 145939 | 817 | 347721 | 145122 | 0.559823 | 99.440177 | 55559.033613 | 136028.125 | 2922.02521 | 799.875 |
5 | 2025-04-02 00:00:00.000 | 154895 | 764 | 346904 | 154131 | 0.493237 | 99.506763 | 54793.101695 | 137431.5 | 2939.864407 | 748 |
6 | 2025-04-01 00:00:00.000 | 155707 | 697 | 346140 | 155010 | 0.447636 | 99.552364 | 53937.529915 | 130452.25 | 2958.461538 | 698.375 |
7 | 2025-03-31 00:00:00.000 | 94010 | 830 | 345443 | 93180 | 0.882885 | 99.117115 | 53060.206897 | 123214.75 | 2977.956897 | 665.625 |
8 | 2025-03-30 00:00:00.000 | 152720 | 832 | 344613 | 151888 | 0.544788 | 99.455212 | 52704.121739 | 121100.5 | 2996.634783 | 615.875 |
9 | 2025-03-29 00:00:00.000 | 147301 | 748 | 343781 | 146553 | 0.507804 | 99.492196 | 51826.789474 | 114349 | 3015.622807 | 557.75 |
10 | 2025-03-28 00:00:00.000 | 102092 | 888 | 343033 | 101204 | 0.869804 | 99.130196 | 50981.884956 | 108588.75 | 3035.690265 | 518 |
11 | 2025-03-27 00:00:00.000 | 135561 | 823 | 342145 | 134738 | 0.607107 | 99.392893 | 50525.544643 | 106782.75 | 3054.866071 | 470 |
12 | 2025-03-26 00:00:00.000 | 157166 | 402 | 341322 | 156764 | 0.255781 | 99.744219 | 49759.459459 | 108763.875 | 3074.972973 | 666.875 |
13 | 2025-03-25 00:00:00.000 | 99061 | 367 | 340920 | 98694 | 0.370479 | 99.629521 | 48783.036364 | 100749.625 | 3099.272727 | 754 |
14 | 2025-03-24 00:00:00.000 | 97807 | 435 | 340553 | 97372 | 0.444753 | 99.555247 | 48321.770642 | 99879.125 | 3124.33945 | 836.875 |
15 | 2025-03-23 00:00:00.000 | 77096 | 432 | 340118 | 76664 | 0.56034 | 99.43966 | 47863.574074 | 106706.75 | 3149.240741 | 1960.375 |
16 | 2025-03-22 00:00:00.000 | 98708 | 367 | 339686 | 98341 | 0.371804 | 99.628196 | 47590.373832 | 107432.625 | 3174.635514 | 1974.25 |
17 | 2025-03-21 00:00:00.000 | 101219 | 430 | 339319 | 100789 | 0.424821 | 99.575179 | 47108.132075 | 112125.25 | 3201.122642 | 7260.25 |
18 | 2025-03-20 00:00:00.000 | 87644 | 504 | 338889 | 87140 | 0.575054 | 99.424946 | 46592.790476 | 110948.375 | 3227.514286 | 7544.125 |
19 | 2025-03-19 00:00:00.000 | 151410 | 2398 | 338385 | 149012 | 1.583779 | 98.416221 | 46198.067308 | 110380.125 | 3253.701923 | 7579.125 |
20 | 2025-03-18 00:00:00.000 | 93052 | 1099 | 335987 | 91953 | 1.18106 | 98.81894 | 45176.592233 | 101513.875 | 3262.009709 | 7525.125 |
BlockTrackernew vs returning users
Updated 2025-04-06
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('day', block_timestamp) as date,
count(DISTINCT from_address) as DAU
FROM ink.core.fact_transactions
GROUP BY date
)
,new as (
SELECT
date_trunc('day', first_tx) as date,
count(DISTINCT user) as new_user
FROM (
SELECT
from_address as user,
min(block_timestamp) as first_tx
FROM ink.core.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: 16 days ago
...
122
13KB
3s