MFD-6247Untitled Query
    Updated 2022-11-06
    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