Flipside Teamproject users growth (Near Ecosystem Scope)
    Updated 2024-11-12
    -- forked from project users growth (Near Ecosystem Scope) @ https://flipsidecrypto.xyz/edit/queries/bcdf2b95-1108-4ea9-86af-31d932a7ab61

    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 PROJECT_NAME, SIGNER_ID as user, min(BLOCK_TIMESTAMP) as min_date
    from projects_events
    group by 1,2
    QUALIFY row_number() OVER (partition by user order by min_date asc) = 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 replace(PROJECT_NAME,' (', ' (')=initcap('{{project_name}}')
    and min_date>='{{starting_date}}'
    group by 1
    order by 2 desc

    Last run: 3 months ago
    DATE
    New User
    Total Users
    1
    2023-09-23 00:00:00.00082613811
    2
    2023-09-03 00:00:00.0006089440
    3
    2023-09-02 00:00:00.0005648832
    4
    2023-09-01 00:00:00.0005158268
    5
    2023-09-24 00:00:00.00045414265
    6
    2023-09-08 00:00:00.00045210274
    7
    2024-07-05 00:00:00.00043123386
    8
    2023-09-20 00:00:00.00040012559
    9
    2023-09-26 00:00:00.00034314870
    10
    2022-11-07 00:00:00.000323573
    11
    2023-10-02 00:00:00.00032016345
    12
    2023-08-31 00:00:00.0003197753
    13
    2023-09-29 00:00:00.00027215609
    14
    2023-09-25 00:00:00.00026214527
    15
    2023-09-27 00:00:00.00025115121
    16
    2023-10-06 00:00:00.00025017298
    17
    2023-10-03 00:00:00.00024516590
    18
    2023-10-04 00:00:00.00024416834
    19
    2023-10-01 00:00:00.00023916025
    20
    2024-03-26 00:00:00.00023622491
    ...
    685
    24KB
    520s