baymatWeekly Whales Activities(Staking)
Updated 2023-02-15
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- SQL credits goes to https://app.flipsidecrypto.com/velocity/queries/897ec395-b1e3-4ca0-8925-31b37395ce0f
WITH transfer_in AS (
SELECT
tx_receiver,
sum (deposit/pow(10,24)) AS transfer_in
FROM near.core.fact_transfers
GROUP BY 1
),
transfer_out AS (
SELECT
tx_signer,
sum (deposit/pow(10,24)) AS transfer_out
FROM near.core.fact_transfers
GROUP BY 1
),
whales AS (
SELECT
tx_receiver AS wallet,
sum (transfer_in - transfer_out) AS balance
FROM transfer_in ins JOIN transfer_out out ON ins.tx_receiver = out.tx_signer
WHERE tx_receiver NOT IN ('aurora', 'wrap.near', 'binancecold3.near', 'proximity-prime.near', 'meta-pool.near', 'nearcoldtree.near', 'v2-nearx.stader-labs.near') --, 'f6bd6ba459446b7b6fca71707779de9473af56f8.lockup.near', 'ec838c99348c4b5a8859a3ca9f44eb136bfa9a01.lockup.near', 'd391f37d5a889a724170f44b2b1eff818c7e20bd.lockup.near', 'd54d5a195b43cedb233c71b5f1bc3239893b8122.lockup.near', '2da34f0cc57e061f18c66dbd4268cc205b4f825d.lockup.near')
AND tx_receiver NOT LIKE 'nfe%' AND tx_receiver NOT LIKE '%protocol%' AND tx_receiver NOT LIKE '%binance%' AND tx_receiver NOT LIKE '%kucoin%'
AND tx_receiver NOT LIKE '%utility%' AND tx_receiver NOT LIKE '%app%' AND tx_receiver NOT LIKE '%spin-fi%' AND tx_receiver NOT LIKE '%market%' AND tx_receiver NOT LIKE '%manager%'
AND tx_receiver NOT LIKE '%usn%' AND tx_receiver NOT LIKE '%token%' AND tx_receiver NOT LIKE '%lockup%'
--wrap.near, proximity-prime.near, meta-pool.near, nearcoldtree.near, aurora, nfeco01.near, nfendowment01.near, linear-protocol.near, v2-nearx.stader-labs.near, app.nearcrowd,near
GROUP BY 1
HAVING balance > 0
ORDER BY 2 DEsc
LIMIT 20
),
stake AS (
SELECT
tx_signer AS whale,
pool_address AS validator,
sum(stake_amount/1e24) as stake_amount
FROM near.core.dim_staking_actions JOIN whales ON tx_signer = wallet
Run a query to Download Data