flipside

    2.01 Total Data Transactions Top active users
    ZSaed2.01 Total Data Transactions Top active users
    Updated 2023-02-28
    -- forked from 252c64e3-f2be-4445-bf26-dce4fd7509a8

    -- forked from 88c1ec71-daa6-4e8c-861a-ff453eac3b0a

    -- forked from 972e8bb3-5ee6-490d-9b13-ea94c6732944

    with users as (select
    TX_HASH, actions:predecessor_id as TX_SIGNER , BLOCK_TIMESTAMP
    from
    near.core.fact_receipts
    where
    BLOCK_TIMESTAMP::date > '2022-12-18'
    and RECEIVER_ID = 'nethmap.near'
    and ACTIONS:receipt:Action:actions[0]:FunctionCall:method_name::string = 'set'
    )
    , receipts as (
    select a.*, a.actions:predecessor_id as TX_SIGNER
    from near.core.fact_receipts a JOIN users b on (a.actions:predecessor_id = b.TX_SIGNER or a.RECEIVER_ID= b.TX_SIGNER)
    where
    a.BLOCK_TIMESTAMP::date> '2022-12-18'
    )
    ,fail as (
    select * from near.core.fact_receipts
    where tx_hash in (select tx_hash from receipts )
    and STATUS_VALUE:Failure is not null
    ),
    success_receipts as (
    select * from receipts where tx_hash not in (select tx_hash from fail )
    )
    , call as (
    select a.*, b.RECEIVER_ID,b.TX_SIGNER
    from near.core.fact_actions_events_function_call a JOIN success_receipts b on a.tx_hash = b.tx_hash
    where a.BLOCK_TIMESTAMP::date> '2022-12-18'
    )
    -- find date of add_key tarnsaction for each users
    ,add_key as (--find wallet that hava add key
    Run a query to Download Data