Moeweekly projects in New user count copy
    Updated 2023-09-03
    with base as (
    select
    LABEL_TYPE as sector,
    *,
    row_number() over (
    partition by
    TX_SIGNER
    order by
    block_timestamp
    ) as row_num
    from
    near.core.fact_transactions
    left join near.core.dim_address_labels on TX_RECEIVER = address
    where
    PROJECT_NAME is not null
    )
    ,
    mid as
    (select
    block_timestamp,
    sector,
    TX_SIGNER
    from
    base
    where
    row_num = 1
    )

    select
    date_trunc(week, block_timestamp)::date as week ,
    case when sector is null then 'other' else sector end as sectors ,
    count(distinct tx_signer) as new_users ,
    row_number()over(partition by week order by new_users desc) as rn
    from
    mid
    Run a query to Download Data