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
QueryRunArchived: QueryRun has been archived