flipside

NakedCollectorsorry-sapphire

Updated 2025-01-25

WITH whale_accounts AS (

-- First identify whale accounts (holders with significant balances)

SELECT

owner,

mint,

AVG(balance) as avg_balance,

COUNT(*) as transaction_count

FROM solana.core.fact_token_balances

WHERE balance > 100000 -- Adjust threshold as needed

GROUP BY owner, mint

HAVING transaction_count > 10

),

whale_sells AS (

-- Track whale selling activity through DEX swaps

SELECT

s.swap_from_mint,

s.swap_from_symbol,

s.swapper,

COUNT(*) as sell_count,

SUM(s.swap_from_amount) as total_sold_amount,

SUM(s.swap_from_amount_usd) as total_sold_usd,

AVG(s.swap_from_amount_usd) as avg_sale_size_usd

FROM solana.defi.ez_dex_swaps s

INNER JOIN whale_accounts w

ON s.swapper = w.owner

AND s.swap_from_mint = w.mint

WHERE s.block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP())

GROUP BY s.swap_from_mint, s.swap_from_symbol, s.swapper

),

token_metrics AS (

-- Aggregate metrics per token

SELECT

swap_from_mint,

swap_from_symbol,

Last run: about 2 months ago

	TOKEN_SYMBOL	TOKEN_ADDRESS	UNIQUE_WHALE_SELLERS	TOTAL_SELL_TRANSACTIONS	TOTAL_TOKEN_VOLUME_SOLD	TOTAL_USD_VOLUME_SOLD	AVG_WHALE_SALE_SIZE_USD	SELLING_PRESSURE_RANK
1	USDC	EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v	4690	4857775	14128143079.3716	14127984962.16	42288.490236776	1
2	USDT	Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB	624	1198731	1381063250.85112	1380021460.56	19075.299751312	2
3	TRUMP	6p6xgHyF7AeE6TZkSmFsko444wqoP15icUSqi2jfGiPN	190	220140	39283129.673714	1347093967.42	367983.95037978	3
4	PEPEAI	B4afKumM2AkrrZZbia94bzrKdxTHp1iv7QgPNp611kiA	5	32	4840207061722.52	1198170483.9	57722329.4202454	4
5	PENGU	2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv	1341	660066	23218925897.6855	746887000.7	4862.830041014	5
6	FARTCOIN	9BB6NFEcjBCtnNLFko2FqVQBq8HHM13kCyYcdQbgpump	1098	390235	526634495.298484	726227815.03	29131.355897251	6
7	AI16Z	HeLp6NuQkmYB4pYWo2zYs22mESHXPQYzXbB8n4V98jwC	404	283394	396015478.012536	482509850.49	24473.363540423	7
8	SWARMS	74SBV4zDXxTRgv1pEMoECskKBkZHc2yGPnc7GYVepump	1494	197119	2317396303.14444	431477835.8	10864.755803618	8
9	MELANIA	FUAfBo2jgks6gB4Z4LfZkqSZgzNucisEHqnNebaRxM1P	69	86077	75445483.659479	410581908.22	776611.19429918	9
10	ARC	61V8vBaqAGMpgDQi4JcAwo1dmBGHsyhzodcPqnEVpump	1222	148547	785600652.334694	292008609.53	13242.46375732	10
11	VINE	6AJcP7wuLwmRYLBNbi825wgguaPsWzPBEHcHndpRpump	1117	83650	1157529201.99071	254830821.47	12447.598539679	11
12	GRIFFAIN	KENJSUYLASHUMfHyy5o4Hp2FdNqZg1AsUPhfH2kYvEP	1038	145743	553870355.720803	229971476.02	11094.527777839	12
13	PIPPIN	Dfh5DzRgSvvCFDoYc2ciTkMrbDfRKybA4SoFbPmApump	1382	107938	2020236157.70645	220888768.15	10107.997993675	13
14	AVA	DKu9kykSfbN5LBfFXtNNDPaX35o4Fv6vJ9FKk7pZpump	847	143789	1287945380.2066	209697439.63	11614.236780263	14
15	BONK	DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263	7745	719856	6072500829535.97	196678295.9	322.344369411	15
16	ZEREBRO	8x5VqbHA8D7NkD52uNuS5nnt3PwA8pLD34ymskeSo2Wn	918	135698	619065498.160308	195162246.64	13199.076546338	16
17	UFD	eL5fUxj2J4CiQsmW85k5FG9DvuQjjUoBHoQBi2Kpump	1593	42170	1100021771.37311	191385450.63	8044.581720555	17
18	JUP	JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN	41	179196	199012471.023708	179265874.53	5837.962096734	18
19	LLM	98mb39tPFKQJ4Bif8iVg9mYb9wsfPZgpgN1sxoVTpump	1839	84990	3084930110.14551	158863877.94	5708.444606677	19
20	BUZZ	9DHe3pycTuymFk4H4bbPoAJ4hQrr2kaLDF6J6aAKpump	1421	89690	1649388835.40567	151849008.95	7040.879188297	20

...

1943

208KB

356s