DATE | NUM_USERS | NUM_NEW_USERS | NUM_RETURNING_USERS | CUM_NEW_USERS | COHORT1_RETENTION_COUNT | COHORT1_COUNT | COHORT1_RETENTION_RATE | COHORT2_RETENTION_COUNT | COHORT2_COUNT | COHORT2_RETENTION_RATE | COHORT3_RETENTION_COUNT | COHORT3_COUNT | COHORT3_RETENTION_RATE | COHORT4_RETENTION_COUNT | COHORT4_COUNT | COHORT4_RETENTION_RATE | COHORT5_RETENTION_COUNT | COHORT5_COUNT | COHORT5_RETENTION_RATE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2023-01-02 00:00:00.000 | 238851 | 238851 | 0 | 238851 | 39881 | 39881 | 1 | 84783 | 25389 | 20797 | 17453 | ||||||||
2 | 2023-01-09 00:00:00.000 | 579853 | 486422 | 93431 | 725273 | 19144 | 39881 | 0.4800280835 | 84783 | 84783 | 1 | 25389 | 20797 | 17453 | ||||||
3 | 2023-01-16 00:00:00.000 | 358907 | 167258 | 191649 | 892531 | 15743 | 39881 | 0.3947493794 | 23692 | 84783 | 0.2794428128 | 25389 | 25389 | 1 | 20797 | 17453 | ||||
4 | 2023-01-23 00:00:00.000 | 366176 | 147783 | 218393 | 1040314 | 15783 | 39881 | 0.3957523633 | 23751 | 84783 | 0.2801387071 | 4161 | 25389 | 0.1638898736 | 20797 | 20797 | 1 | 17453 | ||
5 | 2023-01-30 00:00:00.000 | 362441 | 134782 | 227659 | 1175096 | 14574 | 39881 | 0.3654371756 | 22156 | 84783 | 0.2613259734 | 3841 | 25389 | 0.15128599 | 2869 | 20797 | 0.1379525893 | 17453 | 17453 | 1 |
6 | 2023-02-06 00:00:00.000 | 339943 | 112206 | 227737 | 1287302 | 13026 | 39881 | 0.3266216996 | 24525 | 84783 | 0.2892678957 | 3212 | 25389 | 0.1265114814 | 2329 | 20797 | 0.1119873059 | 1940 | 17453 | 0.1111556752 |
7 | 2023-02-13 00:00:00.000 | 358532 | 134808 | 223724 | 1422110 | 12173 | 39881 | 0.3052330684 | 20441 | 84783 | 0.2410978616 | 3078 | 25389 | 0.1212336051 | 2147 | 20797 | 0.1032360437 | 1789 | 17453 | 0.1025038675 |
8 | 2023-02-20 00:00:00.000 | 333882 | 112394 | 221488 | 1534504 | 11255 | 39881 | 0.2822145884 | 19596 | 84783 | 0.2311312409 | 3163 | 25389 | 0.1245815117 | 2261 | 20797 | 0.1087176035 | 1797 | 17453 | 0.1029622414 |
9 | 2023-02-27 00:00:00.000 | 293803 | 95471 | 198332 | 1629975 | 10230 | 39881 | 0.2565131266 | 16885 | 84783 | 0.1991554911 | 2569 | 25389 | 0.1011855528 | 1768 | 20797 | 0.08501226138 | 1587 | 17453 | 0.09092992609 |
10 | 2023-03-06 00:00:00.000 | 301084 | 99401 | 201683 | 1729376 | 9650 | 39881 | 0.2419698603 | 17366 | 84783 | 0.2048287982 | 2509 | 25389 | 0.09882232463 | 1744 | 20797 | 0.08385824879 | 1437 | 17453 | 0.08233541511 |
11 | 2023-03-13 00:00:00.000 | 269205 | 75079 | 194126 | 1804455 | 9581 | 39881 | 0.2402397131 | 15777 | 84783 | 0.1860868334 | 2328 | 25389 | 0.09169325298 | 1655 | 20797 | 0.0795787854 | 1274 | 17453 | 0.07299604652 |
12 | 2023-03-20 00:00:00.000 | 344335 | 93197 | 251138 | 1897652 | 9871 | 39881 | 0.2475113463 | 20210 | 84783 | 0.2383732588 | 2976 | 25389 | 0.1172161172 | 2063 | 20797 | 0.09919699957 | 1694 | 17453 | 0.09706067725 |
13 | 2023-03-27 00:00:00.000 | 279508 | 80853 | 198655 | 1978505 | 8939 | 39881 | 0.2241418219 | 13927 | 84783 | 0.1642664213 | 2325 | 25389 | 0.09157509158 | 1640 | 20797 | 0.07885752753 | 1465 | 17453 | 0.08393972383 |
14 | 2023-04-03 00:00:00.000 | 432101 | 102218 | 329883 | 2080723 | 10585 | 39881 | 0.2654146085 | 26661 | 84783 | 0.3144616256 | 3632 | 25389 | 0.1430540785 | 2581 | 20797 | 0.1241044381 | 2106 | 17453 | 0.1206669341 |
15 | 2023-04-10 00:00:00.000 | 297345 | 80198 | 217147 | 2160921 | 8275 | 39881 | 0.2074922896 | 16155 | 84783 | 0.1905452744 | 2378 | 25389 | 0.09366260979 | 1737 | 20797 | 0.08352166178 | 1269 | 17453 | 0.07270956283 |
16 | 2023-04-17 00:00:00.000 | 355038 | 88909 | 266129 | 2249830 | 8951 | 39881 | 0.2244427171 | 20624 | 84783 | 0.2432563132 | 2852 | 25389 | 0.1123321123 | 1835 | 20797 | 0.08823387989 | 1472 | 17453 | 0.08434080101 |
17 | 2023-04-24 00:00:00.000 | 256322 | 66275 | 190047 | 2316105 | 7666 | 39881 | 0.19222186 | 13299 | 84783 | 0.156859276 | 2001 | 25389 | 0.07881365946 | 1473 | 20797 | 0.0708275232 | 1175 | 17453 | 0.06732366928 |
18 | 2023-05-01 00:00:00.000 | 253215 | 52096 | 201119 | 2368201 | 7960 | 39881 | 0.1995937915 | 15255 | 84783 | 0.1799299388 | 2023 | 25389 | 0.07968017645 | 1416 | 20797 | 0.06808674328 | 1283 | 17453 | 0.07351171718 |
19 | 2023-05-08 00:00:00.000 | 310603 | 59633 | 250970 | 2427834 | 8182 | 39881 | 0.205160352 | 18466 | 84783 | 0.2178030973 | 2415 | 25389 | 0.09511993383 | 1756 | 20797 | 0.08443525508 | 1249 | 17453 | 0.07156362803 |
20 | 2023-05-15 00:00:00.000 | 226739 | 51622 | 175117 | 2479456 | 7061 | 39881 | 0.1770517289 | 13102 | 84783 | 0.154535697 | 1683 | 25389 | 0.06628855016 | 1198 | 20797 | 0.05760446218 | 839 | 17453 | 0.04807196471 |
0xHaM-dRetention
Updated 2025-04-30
999
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 Retention @ https://flipsidecrypto.xyz/edit/queries/c0cb060e-fca1-4c21-9399-e460b535d396
-- forked from Retention by Dune @ https://flipsidecrypto.xyz/edit/queries/821afa83-97c1-49de-bc53-f515ada55660
WITH
users AS (
select
block_timestamp,
tx_hash,
TX_SIGNER
FROM near.core.fact_transactions
WHERE block_timestamp::date > '2023-01-01'
),
firstUserOccurrences AS (
SELECT
TX_SIGNER,
MIN(users.BLOCK_TIMESTAMP::date) AS firstTradeDate
FROM
users
GROUP BY
TX_SIGNER
),
statsByDay AS (
SELECT
date_trunc('week',BLOCK_TIMESTAMP)::date as date,
COUNT(DISTINCT (users.TX_SIGNER)) AS num_users,
COALESCE(COUNT(DISTINCT (firstUserOccurrences.TX_SIGNER)), 0) AS num_New_users,
COUNT(DISTINCT (users.TX_SIGNER)) - COALESCE(COUNT(DISTINCT (firstUserOccurrences.TX_SIGNER)), 0) AS num_Returning_users,
SUM(
COALESCE(COUNT(DISTINCT (firstUserOccurrences.TX_SIGNER)), 0)
) OVER (
ORDER BY
date
) AS cum_New_users
FROM
users
Last run: about 2 months ago
...
122
21KB
972s