amelia-leeUntitled Query
Updated 2022-08-11
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,
FROM_ADDRESS as wallets
from ethereum.core.fact_transactions
where substring(INPUT_DATA, 202, 1)='1'
and ORIGIN_FUNCTION_SIGNATURE in ('0xc6878519') -- Solana to Ethereum
and TO_ADDRESS='0x3ee18b2214aff97000d974cf647e7c347e8fa585'
and STATUS='SUCCESS'
and BLOCK_TIMESTAMP>=CURRENT_DATE-90
),
tb2 as (
select
x.BLOCK_TIMESTAMP,
x.FROM_ADDRESS,
x.TO_ADDRESS,
x.tx_hash,
ROW_NUMBER() OVER (partition by FROM_ADDRESS order by x.BLOCK_TIMESTAMP) as t_n
from ethereum.core.fact_token_transfers x
join tb1 y on x.from_address = y.wallets and x.block_timestamp>y.block_timestamp
order by 1
)
select
LABEL_TYPE,
count(distinct FROM_ADDRESS) as users,
count(tx_hash) as count_txn
-- sum(users) over (partition by LABEL_TYPE order by day) as cum_users,
-- sum(count_txn) over (partition by LABEL_TYPE order by day) as cum_txn
from tb2 q join ethereum.core.dim_labels w on q.TO_ADDRESS= w.address
where LABEL_TYPE not in ('chadmin','operator')
and t_n=1
group by 1
order by 3 desc
Run a query to Download Data