Flipside Teamproject users growth (Project Scope)
    Updated 2024-11-27
    -- forked from project users growth (Project Scope) @ https://flipsidecrypto.xyz/edit/queries/e4ceafea-9b63-4112-b678-ff67f0bfb45c

    with near_projects as
    (select ADDRESS, initcap(ADDRESS_NAME) as ADDRESS_NAME,
    initcap(PROJECT_NAME) as PROJECT_NAME,
    initcap(LABEL_TYPE) as LABEL_TYPE
    from near.core.dim_address_labels
    where LABEL_TYPE not in ('cex','token') and PROJECT_NAME is not null
    ),

    projects_events as (
    select BLOCK_TIMESTAMP, SIGNER_ID, METHOD_NAME, RECEIVER_ID, ADDRESS_NAME, PROJECT_NAME, LABEL_TYPE, TX_HASH, DEPOSIT/1e24 as Volume
    from near.core.fact_actions_events_function_call
    join near_projects
    on RECEIVER_ID=ADDRESS
    where RECEIPT_SUCCEEDED=true
    ),


    users as
    (
    select SIGNER_ID as user, min(BLOCK_TIMESTAMP) as min_date
    from projects_events
    where replace(PROJECT_NAME,' (', ' (')=initcap('{{project_name}}')
    group by 1
    )

    select date_trunc(day,min_date) as date, count(*) as "New User",
    sum("New User") over (order by date) as "Total Users"
    from users
    where min_date>='{{starting_date}}'
    group by 1
    order by 2 desc

    Auto-refreshes every 3 hours
    QueryRunArchived: QueryRun has been archived