bachibridge1
Updated 2022-11-01
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 tb1 as (select
BLOCK_TIMESTAMP,
ORIGIN_FROM_ADDRESS as users
from ethereum.core.fact_token_transfers
where ORIGIN_TO_ADDRESS = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'
),
tb2 as (
select
x.BLOCK_TIMESTAMP,
x.origin_from_address,
x.origin_to_address,
x.tx_hash,
ROW_NUMBER() OVER (partition by origin_from_address order by x.BLOCK_TIMESTAMP) as t_n
from optimism.core.fact_event_logs x
join tb1 y on x.origin_from_address = y.users and x.block_timestamp>y.block_timestamp
--where t_n=1
order by 1
)
/*select
distinct event_name as first_actions,
count(distinct tx_hash) as counts,
count(distinct origin_from_address) as users
from optimism.core.fact_event_logs
where tx_hash in (select tx_hash from tb2)
group by 1
having first_actions is not null
order by 2 desc*/
select
distinct PROJECT_NAME,
date(block_timestamp) as day,
count(tx_hash) as count_txn
from tb2 q join optimism.core.dim_labels w on q.origin_to_address= w.address
where t_n=1 and block_timestamp >= dateadd(month, -6, getdate())
Run a query to Download Data