MLDZMNneonhld1 copy
Updated 2023-08-22
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
›
⌄
-- forked from neonhld1 @ https://flipsidecrypto.xyz/edit/queries/4fbfef2b-47eb-41d6-b0f6-8e2e831a12e4
-- Thanks to H4wk
WITH token_holdings AS (
SELECT block_timestamp, post_tokens.value:owner AS wallet, post_tokens.value:mint AS token, post_tokens.value:uiTokenAmount:uiAmount AS amount,
ROW_NUMBER() OVER (PARTITION BY wallet, token ORDER BY block_timestamp DESC) AS rn
FROM solana.core.fact_transactions, LATERAL FLATTEN(input => post_token_balances) post_tokens
WHERE SUCCEEDED = 'True'
AND post_tokens.value:owner = wallet
AND post_tokens.value:mint = '31k88G5Mq7ptbRDf3AM13HAq6wRQHXHikR8hik7wPygk'
and wallet not in ('J3HfZ2yorrunhozTMaF57baZ5tdCeQGD56efyHrkA9Gz')
AND block_timestamp >= '2023-01-01'
ORDER BY wallet, token, rn
)
select
avg(amount) as avg_balance,
median(amount) as median_holding,
count(distinct wallet) as total_holders
FROM token_holdings
WHERE rn = 1 AND amount IS NOT NULL AND amount <> ''
order by amount desc
Run a query to Download Data