Alir3zaUntitled Query
    Updated 2022-09-14

    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