DATE | DAU | NEW_ACCOUNT | CUM_ACCOUNT | RETURNING_ACCOUNT | NEW_ACCOUNT_PERCENT | RETURNING_ACCOUNT_PERCENT | |
|---|---|---|---|---|---|---|---|
1 | 2025-04-01 00:00:00.000 | 3484173 | 2299988 | 90928704 | 1184185 | 66.012451 | 33.987549 |
2 | 2025-03-31 00:00:00.000 | 8364674 | 6417848 | 88628716 | 1946826 | 76.72562 | 23.27438 |
3 | 2025-03-30 00:00:00.000 | 7197912 | 5193450 | 82210868 | 2004462 | 72.152174 | 27.847826 |
4 | 2025-03-29 00:00:00.000 | 6994690 | 4764895 | 77017418 | 2229795 | 68.121604 | 31.878396 |
5 | 2025-03-28 00:00:00.000 | 6159272 | 3456478 | 72252523 | 2702794 | 56.118288 | 43.881712 |
6 | 2025-03-27 00:00:00.000 | 7034082 | 4752939 | 68796045 | 2281143 | 67.570139 | 32.429861 |
7 | 2025-03-26 00:00:00.000 | 6383823 | 4152256 | 64043106 | 2231567 | 65.043407 | 34.956593 |
8 | 2025-03-25 00:00:00.000 | 7471725 | 5275122 | 59890850 | 2196603 | 70.601126 | 29.398874 |
9 | 2025-03-24 00:00:00.000 | 7083725 | 5128663 | 54615728 | 1955062 | 72.400651 | 27.599349 |
10 | 2025-03-23 00:00:00.000 | 7224312 | 5246061 | 49487065 | 1978251 | 72.616756 | 27.383244 |
11 | 2025-03-22 00:00:00.000 | 7690269 | 5666651 | 44241004 | 2023618 | 73.685992 | 26.314008 |
12 | 2025-03-21 00:00:00.000 | 6775006 | 4791158 | 38574353 | 1983848 | 70.718137 | 29.281863 |
13 | 2025-03-20 00:00:00.000 | 6572545 | 4698938 | 33783195 | 1873607 | 71.493432 | 28.506568 |
14 | 2025-03-19 00:00:00.000 | 5280331 | 3329834 | 29084257 | 1950497 | 63.061085 | 36.938915 |
15 | 2025-03-18 00:00:00.000 | 5534359 | 3350951 | 25754423 | 2183408 | 60.548132 | 39.451868 |
16 | 2025-03-17 00:00:00.000 | 4808529 | 2943165 | 22403472 | 1865364 | 61.20718 | 38.79282 |
17 | 2025-03-16 00:00:00.000 | 4615020 | 2975928 | 19460307 | 1639092 | 64.483534 | 35.516466 |
18 | 2025-03-15 00:00:00.000 | 4944542 | 2768883 | 16484379 | 2175659 | 55.998776 | 44.001224 |
19 | 2025-03-14 00:00:00.000 | 3375447 | 1466042 | 13715496 | 1909405 | 43.432529 | 56.567471 |
20 | 2025-03-13 00:00:00.000 | 3646551 | 1708029 | 12249454 | 1938522 | 46.839575 | 53.160425 |
messarinew vs returning users copy
Updated 2 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
›
⌄
-- forked from BlockTracker / new vs returning users @ https://flipsidecrypto.xyz/BlockTracker/q/AgcU2zisUql-/new-vs-returning-users
with DAU_u as (
SELECT
date_trunc('{{granularity}}', block_timestamp) as date,
count(DISTINCT from_address) as DAU
FROM monad.testnet.fact_transactions
where date >= '2025-02-18'
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 monad.testnet.fact_transactions
GROUP BY 1)
where date >= '2025-02-18'
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
Last run: 2 days ago
43
3KB
40s