mariyaFTX Wallet Balance
Updated 2022-11-09
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
›
⌄
--CREDIT: CarlowsOs https://app.flipsidecrypto.com/velocity/queries/23b11286-fbb5-4c3b-8925-2a35fab3ef0c
WITH ftx AS (--mirar crosschain
SELECT address
FROM ethereum.core.dim_labels
WHERE address_name ilike '%ftx%'
AND label_subtype = 'hot_wallet'
), balance_tmp AS (
SELECT contract_address, symbol, CASE
WHEN contract_address IN ('0xdac17f958d2ee523a2206206994597c13d831ec7','0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48','0x4fabb145d64652a948d72533023f6e7a623c7c53','0x6b175474e89094c44da98b954eedeac495271d0f','0x853d955acef822db058eb8505911ed77f175b99e','0x8e870d67f660d95d5be530380d0ec0bd388289e1',lower('0x0000000000085d4780B73119b644AE5ecd22b376'),'0x056fd409e1d7a124bd7017459dfea2f387b6d5cd',lower('0x0C10bF8FcB7Bf5412187A595ab97a3609160b5c6')) THEN 'Stablecoin'
-- usdt, usdc, buds, dai, frax, usdp, tusd, gusd, dusd
ELSE 'Other'
END AS token_type, DATE_TRUNC('HOUR', last_activity_block_timestamp) AS hour, current_bal
FROM ethereum.core.ez_current_balances
WHERE
user_address IN (SELECT address FROM ftx)
), aux_prices AS (
SELECT MAX(hour) AS latest_today
FROM ethereum.core.fact_hourly_token_prices
WHERE
hour::DATE = CURRENT_DATE-1
), FINAL AS (
SELECT b.contract_address, b.symbol, token_type, SUM(current_bal) AS token_balance, SUM(current_bal*price) AS balance
FROM balance_tmp b
JOIN ethereum.core.fact_hourly_token_prices p
ON b.contract_address = p.token_address
JOIN aux_prices
ON p.hour = latest_today
GROUP BY 1, 2, 3
HAVING balance IS NOT NULL
)
SELECT *
FROM
(select contract_address, symbol, token_type, balance, row_number() over (order by balance desc) as rank
from FINAL
) ranks
WHERE rank <= 50--balance_usd <> 0
Run a query to Download Data