ramishoow2023-02-14 11:49 AM
Updated 2023-02-14
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
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