Flipside TeamMAUs by # of Projects (Excluding Token & CEX)
    Updated 2024-11-14
    -- forked from MAUs by # of Projects (Excluding Token & CEX) @ https://flipsidecrypto.xyz/edit/queries/62be059a-82b6-4638-8941-b85bda54a8f9


    with base as (
    select
    block_timestamp,
    tx_signer as user_address,
    project_name
    from near.core.fact_transactions
    inner join (select address, project_name from near.core.dim_address_labels
    where label_type not in ('token','cex')
    and label_subtype not in ('token_contract', 'nf_token_contract')
    and project_name not in ('kaikai', 'sweat'))
    on address = tx_receiver
    where tx_succeeded=true
    and tx_signer not in (select distinct address from near.core.dim_address_labels)
    and block_timestamp::date >= '{{starting_date}}'
    -- qualify(row_number() over (partition by user_address, project_name order by block_timestamp)) = 1
    ),


    final as (
    select
    a.*,
    row_number() over (partition by a.user_address order by block_timestamp) as rank
    from base a )

    select date,
    case when projects=1 then '1 Project'
    when projects=2 then '2 Project'
    when projects>2 and projects<6 then '3 to 5 Project'
    else '5+ Projects'
    end as "Type",
    count(distinct user_address) as "# of Users"
    from (
    select