ramishoow2023-02-14 11:49 AM
    Updated 2023-02-14
    with ramishow as (with swap as (select trader, count(distinct tx_hash) as minting_txn
    from near.core.ez_dex_swaps enm group by 1 ), mint as (select tx_signer as trader, count(distinct tx_hash) as minting_txn
    from near.core.ez_nft_mints group by 1 ), list as (select mint.trader as ad1, swap.trader as ad2
    from swap inner join mint on mint.trader=swap.trader ), bal as ( with tb AS ( select TX_SIGNER AS user,
    - sum(DEPOSIT / 1e24) AS amount from near.core.fact_transfers group by 1 union ALL select
    TX_SIGNER AS user, - sum(tx:actions[0]:FunctionCall:deposit / pow(10, 24)) AS amount from
    near.core.fact_actions_events_function_call A inner join near.core.fact_transactions B on A.TX_HASH = B.TX_HASH
    where METHOD_NAME in ('stake', 'deposit_and_stake') group by 1 union ALL select TRADER AS user,
    - sum(AMOUNT_OUT) AS amount from near.core.ez_dex_swaps where TOKEN_OUT in ('stNEAR', 'NEAR', 'wNEAR')
    group by 1 union ALL select TX_SIGNER AS user, - sum(TRANSACTION_FEE / 1e24) AS amount
    from near.core.fact_transactions group by 1 union ALL select TX_RECEIVER User, sum(DEPOSIT / 1e24) AS amount
    from near.core.fact_transfers group by 1 union ALL select TRADER AS user, sum(AMOUNT_IN) AS amount
    from near.core.ez_dex_swaps where TOKEN_IN in ('stNEAR', 'NEAR', 'wNEAR') group by 1
    union ALL select TX_SIGNER AS user, sum(try_parse_json(args):balance / 1e24) AS Amount
    from near.core.fact_actions_events_function_call A inner join near.core.fact_transactions B on A.TX_HASH = B.TX_HASH
    where METHOD_NAME in ('unstake') and try_parse_json(args):balance LIKE '%[^0-9]%' group by
    1 ) select user AS address, dal.address as dal_address, sum(amount) AS Balance from
    tb left join near.core.dim_address_labels dal on tb.user = dal.address group by 1,2 order by
    3 desc) select bal.address, balance from bal inner join list on list.ad1=bal.address order by 2 desc limit 50)
    select ramishow.address, tx_signer as "user address", sum(stake_amount/10e24) as "total stake amt"
    from near.core.dim_staking_actions dsa right join ramishow on dsa.tx_signer=ramishow.address
    where action = 'Stake' group by 1,2 limit 100
    --where action = 'Stake' group by 1,2 limit 100
    Run a query to Download Data