MasiTOTAL NUMBER OF USERS AND TRANSACTIONS TO RINKBEY
Updated 2022-10-17
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 gnosis as ( select min(block_timestamp::date) as day,
from_address
from gnosis.core.fact_transactions
group by 2)
,
new_user as ( select day,
from_address
from gnosis
where day >= CURRENT_DATE - 60)
,
tx_by_new as ( select 'New User' as type,
trunc(block_timestamp,'day') as day,
from_address,
count(DISTINCT tx_hash) as count_tx,
sum(count_tx) over (order by day asc) as cumulative_tx
from gnosis.core.fact_transactions
where day >= CURRENT_DATE - 60
and from_address in (select from_address from new_user)
group by 1,2,3
UNION
select 'Old User' as type,
trunc(block_timestamp,'day') as day,
from_address,
count(DISTINCT tx_hash) as count_tx,
sum(count_tx) over (order by day asc) as cumulative_tx
from gnosis.core.fact_transactions
where day >= CURRENT_DATE - 60
and from_address not in (select from_address from new_user)
group by 1,2,3)
select trunc(block_timestamp,'day') as day,
case when to_address = '0xc38d4991c951fe8bce1a12beef2046ef36b0fa4a' then 'Rinkeby'
else 'Other' end as status,
count(DISTINCT from_address) as total_user,
count(DISTINCT tx_hash) as count_tx
Run a query to Download Data