Flipside Teamusers growth
    Updated 2024-11-12
    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 BLOCK_TIMESTAMP>='{{starting_date}}'
    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
    group by 1
    order by 2 desc


    Last run: about 2 months ago
    DATE
    New User
    Total Users
    1
    2023-12-22 00:00:00.000139105214694404
    2
    2023-12-21 00:00:00.000118457013303352
    3
    2023-12-28 00:00:00.000111326320278969
    4
    2023-12-23 00:00:00.000107414615768550
    5
    2023-12-27 00:00:00.000106923519165706
    6
    2023-12-26 00:00:00.000103820318096471
    7
    2023-12-24 00:00:00.00089346016662010
    8
    2023-12-19 00:00:00.00073940711526046
    9
    2024-01-25 00:00:00.00070535926381932
    10
    2023-12-20 00:00:00.00059273612118782
    11
    2023-12-18 00:00:00.00058306710786639
    12
    2024-06-08 00:00:00.00055877946231872
    13
    2024-06-09 00:00:00.00050385446735726
    14
    2024-01-13 00:00:00.00049897723077201
    15
    2024-01-12 00:00:00.00049029622578224
    16
    2023-09-04 00:00:00.0004902645540003
    17
    2024-01-24 00:00:00.00047394725676573
    18
    2024-01-14 00:00:00.00047043123547632
    19
    2024-06-07 00:00:00.00044223845673093
    20
    2023-09-05 00:00:00.0004292445969247
    ...
    1092
    44KB
    262s