Abbas_ra21Pass Wallets Cohort Retention
Updated 2023-08-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 https://flipsidecrypto.xyz/TheLaughingMan/q/EETNzgMr7QCN/near-cohort-retention
-- by TheLaughingMan in this Dashboard: https://flipsidecrypto.xyz/TheLaughingMan/death-is-near-K6WoZL
with wallets AS (select
TX_HASH,
substr(LOGS [0], 9, 42) AS Address
from
near.core.fact_receipts
inner join near.core.fact_transactions using(TX_HASH)
where
RECEIVER_ID = 'aurora-silo-dev.near'
and TX_SIGNER = 'relay.aurora'
and TX_STATUS = 'Success' )
,base_table as (
select
FROM_ADDRESS as user
, date_trunc('month', block_timestamp) as date
, min(date_trunc('month', block_timestamp)) over(partition by user) as earliest_date
, datediff(
'month'
, min(date_trunc('month', block_timestamp)) over(partition by user) -- earliest_date
, date_trunc('month', block_timestamp) -- current date in month
) as difference
from aurora.core.fact_transactions
where 1=1
and block_timestamp >= current_timestamp() - interval '1 year'
and from_address in (select Address from Wallets)
)
, count_new_users as(
select
earliest_date
, count(distinct user) as new_users
from base_table
group by 1
)
Run a query to Download Data