DATE_TIME | TOTAL_USER | NEW_ADDRESS | REGULAR_ADDRESS | |
|---|---|---|---|---|
1 | 2023-08-01 00:00:00.000 | 4839 | 4839 | 0 |
2 | 2023-09-01 00:00:00.000 | 18962 | 15596 | 3366 |
3 | 2023-10-01 00:00:00.000 | 21805 | 16472 | 5333 |
4 | 2023-11-01 00:00:00.000 | 16700 | 11175 | 5525 |
5 | 2023-12-01 00:00:00.000 | 19829 | 13651 | 6178 |
6 | 2024-01-01 00:00:00.000 | 26680 | 18753 | 7927 |
7 | 2024-02-01 00:00:00.000 | 35916 | 24303 | 11613 |
8 | 2024-03-01 00:00:00.000 | 72589 | 52569 | 20020 |
9 | 2024-04-01 00:00:00.000 | 84622 | 47861 | 36761 |
10 | 2024-05-01 00:00:00.000 | 85271 | 51908 | 33363 |
11 | 2024-06-01 00:00:00.000 | 73415 | 39920 | 33495 |
12 | 2024-07-01 00:00:00.000 | 51531 | 21435 | 30096 |
13 | 2024-08-01 00:00:00.000 | 49066 | 19805 | 29261 |
14 | 2024-09-01 00:00:00.000 | 38421 | 14186 | 24235 |
15 | 2024-10-01 00:00:00.000 | 168955 | 139335 | 29620 |
16 | 2024-11-01 00:00:00.000 | 73333 | 36935 | 36398 |
17 | 2024-12-01 00:00:00.000 | 115054 | 63276 | 51778 |
18 | 2025-01-01 00:00:00.000 | 70013 | 30431 | 39582 |
freeman_7User retention
Updated 2025-01-23
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 first_interaction as (
SELECT
distinct origin_from_address as address,
--date_trunc('month',block_timestamp) as month
min(date_trunc('month',block_timestamp)) as date_time
from base.defi.ez_dex_swaps
where origin_to_address in (lower('0x6Cb442acF35158D5eDa88fe602221b67B400Be3E'),lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43'), lower('0xBE6D8f0d05cC4be24d5167a3eF062215bE6D18a5'))
--qualify Row_number() over(partition by origin_from_address order by date_trunc('month',block_timestamp) asc)= 1
group by address
)
select
count(address)
from first_interaction
,all_interaction as (
SELECT
origin_from_address as address,
date_trunc('month',block_timestamp) as month
from base.defi.ez_dex_swaps
where origin_to_address in (lower('0x6Cb442acF35158D5eDa88fe602221b67B400Be3E'),lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43'), lower('0xBE6D8f0d05cC4be24d5167a3eF062215bE6D18a5'))
)
,classify as (
select
a.month as date_time,
a.address as address,
case
when f.date_time = a.month then 'new_user'
else 'regular_user'
end as user_info
from all_interaction a
join first_interaction f using(address)
order by 1 desc
)
Last run: about 2 months ago
18
821B
8s