MFD-6247Untitled Query
Updated 2022-11-06Copy Reference Fork
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
›
⌄
with table1 as (
select distinct origin_from_address
from optimism.core.fact_event_logs),
table2 as (
select date_trunc(week,block_timestamp) as date,
origin_to_address,
case when origin_to_address = '0xdef1abe32c034e558cdd535791643c58a13acc10' then '0x Exchange'
when origin_to_address = '0x420000000000000000000000000000000000000f' then 'Gas Price Oracle'
when origin_to_address = '0xa6baa075fb5cf4721b43fe068ee81b56f34fa06d' then '0xa6baa075fb5cf4721b43fe068ee81b56f34fa06d'
when origin_to_address = '0x04b5a1945002e0289aa91a7dc94426a7b6707697' then '0x04b5a1945002e0289aa91a7dc94426a7b6707697'
when origin_to_address = '0x5130f6ce257b8f9bf7fac0a0b519bd588120ed40' then 'Clipper Exchange'
else initcap(project_name) end as destination_label,
count (distinct t1.tx_hash) as TX_Count,
count (distinct t1.origin_from_address) as Users_Count
from optimism.core.fact_event_logs t1 join table1 t2 on t1.origin_from_address = t2.origin_from_address
left outer join optimism.core.dim_labels t3 on t1.origin_to_address = t3.address
where block_timestamp >= '2022-07-01' and block_timestamp < '2022-08-01'
and tx_status = 'SUCCESS'
group by 1,2,3 ),
maintable as (
select *,
row_number() over (partition by date order by Users_Count desc) as RN
from table2)
select * from maintable
where RN <= 5
order by date
Run a query to Download Data