Alir3zaUntitled Query
Updated 2022-09-14
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
transactions as
(
select
origin_from_address,
block_timestamp
from ethereum.core.fact_event_logs
where origin_to_address in ('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1')
and tx_status='SUCCESS'
),
afters as (
select
trunc(x.block_timestamp,'week') as weeks,
address_name,
project_name,
label_type,
label_subtype,
count(distinct tx_hash) as counts
from optimism.core.fact_event_logs x
join transactions y on x.origin_from_address=y.origin_from_address
join optimism.core.dim_labels z on x.contract_address=z.address
and trunc(x.block_timestamp,'day')=trunc(y.block_timestamp,'day') and x.block_timestamp > y.block_timestamp
group by 1,2,3,4,5
order by 3 desc
),
ranks as
(SELECT afters.*,
RANK() OVER(PARTITION BY project_name ORDER BY counts DESC) as pos
FROM afters
),
final as (
SELECT *
FROM ranks
ORDER BY counts asc, 3 DESC)
Run a query to Download Data