SectorS Wallets
Updated 2023-03-25
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
›
⌄
--Note: When funding an account on stake.com users deposit money to a "jump" wallet that then automatically transfers the funds to the stake.com main wallet. This means, starting from the main wallet we need to step back 2 transactions to find the end user who deposited into stake.com
--Find all jump wallets starting from stake.com main wallet. Then find all wallets that funded the jump wallet. This is the depositor wallet/user.
--If airdrop make sure we over filter to ensure only users are listed. If we go the claim route, underfilter because CEX/Stake wallets will never claim their tokens.
--01/09 Using the USD_Amount field limits the data set as data is not always avaialable in USD. Using raw token amounts and getting pricing later will improve amount of data
--Find all Jump/Deposit wallets that Stake.com uses to funnel into their main wallet
With Receive_raw as (
select
ETH_FROM_ADDRESS as wallet,
sum(amount) as Receive
from ethereum.core.ez_eth_transfers
where ETH_TO_ADDRESS = lower('0x974CaA59e49682CdA0AD2bbe82983419A2ECC400') --Stake.com main wallet where funds are deposited
group by 1
UNION ALL
select
FROM_ADDRESS as wallet,
sum(amount) as Receive
from ethereum.core.ez_token_transfers
where TO_ADDRESS = lower('0x974CaA59e49682CdA0AD2bbe82983419A2ECC400') --Stake.com main wallet where funds are deposited
group by 1
)
--filter CEX and other wallets with high tx counts. Jump wallets will all have low tx counts.
, Receive_CEX_filter as (
select
from_address as wallet,
count(*) as Tot_txns
from ethereum.core.fact_transactions
where FROM_ADDRESS in (select wallet from Receive_raw)
group by 1
having count(*) <1000
)
Run a query to Download Data