emirprince95Number Unique Wallets of New Anchor Users
Updated 2021-12-28
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 users_before_three_months as (select distinct(sender), cast('deposit_and_earn' as varchar) as Behavior
from anchor.deposits
where date_trunc('day', block_timestamp) < date_trunc('day', current_date) - 90
and sender is not null
UNION
select distinct(sender), cast('bonding' as varchar) as Behavior
from anchor.bonds
where date_trunc('day', block_timestamp) < date_trunc('day', current_date) - 90
and sender is not null
UNION
select distinct(sender), cast('borrowing' as varchar) as Behavior
from anchor.borrows
where date_trunc('day', block_timestamp) < date_trunc('day', current_date) - 90
and sender is not null
UNION
select distinct(sender), cast('ANC_staking' as varchar) as Behavior
from anchor.gov_staking
where date_trunc('day', block_timestamp) < date_trunc('day', current_date) - 90
and sender is not null
),
new_users_within_last_three_months as (
select distinct(sender), cast('deposit_and_earn' as varchar) as Behavior from anchor.deposits
where sender not in (select sender from users_before_three_months where Behavior='deposit_and_earn')
UNION
select distinct(sender), cast('bonding' as varchar) as Behavior from anchor.bonds
where sender not in (select sender from users_before_three_months where Behavior='bonding')
UNION
select distinct(sender), cast('borrowing' as varchar) as Behavior from anchor.borrows
where sender not in (select sender from users_before_three_months where Behavior='borrowing')
UNION
select distinct(sender), cast('ANC_staking' as varchar) as Behavior from anchor.gov_staking
where sender not in (select sender from users_before_three_months where Behavior='ANC_staking')
)
-- select * from new_users_within_last_three_months
Run a query to Download Data