Flipside TeamNEAR Projects info
    Updated 2024-10-17
    -- forked from NEAR Projects info @ https://flipsidecrypto.xyz/edit/queries/3c55d157-39ec-4761-a377-3097503f5715

    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
    )


    select
    count(*) as "Contract Calls",
    count(distinct tx_hash) as "Transaction",
    count(distinct SIGNER_ID) as "Users",
    sum(volume) as "Volume ($NEAR)",
    avg(volume) as "Average Volume ($NEAR)"
    from projects_events
    where BLOCK_TIMESTAMP>='{{starting_date}}'




    Last run: 3 months ago
    Contract Calls
    Transaction
    Users
    Volume ($NEAR)
    Average Volume ($NEAR)
    1
    3190889355216507858964524942714361289.2190640.223875292981
    1
    66B
    442s