Wallet Breakdown

dannyamahWallet Breakdown

Updated 2025-03-30

WITH token_won AS (

SELECT

event_data:spinner AS spinner,

event_data:value / CASE WHEN event_data:token_address = '0x1' THEN POW(10, 8)

ELSE POW(10, 6)

END AS prize_amount,

CASE WHEN event_data:token_address = '0x1' THEN 'APT' ELSE 'LOON' END AS token

FROM aptos.core.fact_events

WHERE event_type = '0xb588e7842f7fca80cf6d92eeb7a05db0dce6af5e02248374fb4498d96ea23236::spin::SpinEvent'

AND success = TRUE

latest_token_prices AS (

SELECT

token_address,

CASE

WHEN token_address = '0x1::aptos_coin::AptosCoin' THEN 'APT'

WHEN token_address = '0x268d4a7a2ad93274edf6116f9f20ad8455223a7ab5fc73154f687e7dbc3e3ec6::LOON::LOON' THEN 'LOON'

END AS token,

price,

ROW_NUMBER() OVER (PARTITION BY token_address ORDER BY hour DESC) AS rn

FROM aptos.price.ez_prices_hourly

WHERE token_address IN ('0x1::aptos_coin::AptosCoin', '0x268d4a7a2ad93274edf6116f9f20ad8455223a7ab5fc73154f687e7dbc3e3ec6::LOON::LOON')

)

SELECT

spinner, -- Group by spinner (wallet address)

COUNT(*) AS spins, -- Number of spins per wallet

SUM(CASE WHEN t.token = 'APT' THEN prize_amount ELSE 0 END) AS total_apt_won, -- Total APT won per wallet

SUM(CASE WHEN t.token = 'LOON' THEN prize_amount ELSE 0 END) AS total_loon_won, -- Total LOON won per wallet

SUM(CASE WHEN t.token = 'APT' THEN prize_amount * p_apt.price ELSE 0 END) +

SUM(CASE WHEN t.token = 'LOON' THEN prize_amount * p_loon.price ELSE 0 END) AS total_win_usd, -- Total win in USD

(COUNT(CASE WHEN prize_amount > 0 THEN 1 END) / NULLIF(COUNT(*), 0)) * 100 AS win_rate_pct,

COUNT(CASE WHEN prize_amount = 0 THEN 1 END) AS spins_without_prize, -- Number of spins with no prize

Last run: 3 months ago

	SPINNER	SPINS	TOTAL_APT_WON	TOTAL_LOON_WON	TOTAL_WIN_USD	WIN_RATE_PCT	SPINS_WITHOUT_PRIZE	TOTAL_APT_SPENT
1	0x26c3f1e476e6701602952874ba3dfb76c7248effb52634972cf2db0a89bf2cd4	1891	32.3	3368300	1415.257775	18.3501	1544	189.1
2	0xf272d3bc7822f911f858779c171e7456006c10879ded163c61e625e8be6fabfe	774	120.1	961040	992.59502	28.1654	556	77.4
3	0xcd0f902ef08d2e0f2a5f0983f70ead8e40b2b85d73114376ab8cd5a806481e48	1214	29.6	1945990	875.7328075	26.771	889	121.4
4	0xe84392659f703ebbdf0391d71d3a724cd532bf9f8e3a9ea772c9f2c7ac151e05	689	30.2	1470000	703.1595	13.7881	594	68.9
5	0xd4630ca7e5e7e55341dc8371862cefb37b345d1a33168a17d929cf157c1ccada	327	116.5	86900	650.702825	29.3578	231	32.7
6	0xe1f1da8e03c4e7c54205e3d9fb6ff6cd8516cca7c8e6684e0f1ee97ead409206	287	113.7	65320	627.86641	26.4808	211	28.7
7	0x3188cf83e8cf3b0de584688a8249b46be2d197ba962bc9b87a998cb023635e19	521	112.4	83360	627.62468	33.0134	349	52.1
8	0x5a329ae2ebe209f68fd9193953c69a642ebf9659da8cc4fac913c463db9f4f44	230	113.2	63800	624.65015	26.5217	169	23
9	0xd34f6f121ff306b9effc84311382fd92fc5dc0f9dddfe487b2615a3d7f5d1f90	569	107.5	143800	623.92315	31.4587	390	56.9
10	0x73c1d08062827a4ec076f74226c4ea7b31416c17314ab26f0e87be93c11fe6f4	252	112.4	17420	603.276335	33.7302	167	25.2
11	0xcc267d31e969054ef1f9557b7656ccf21cce794822190e6ca180bb98202682a1	375	103.7	28830	561.2924775	36	240	37.5
12	0x4c81d439fb902c166a3019a6f7da61e3c0c4b5d369a9a87cff6946283d41f48f	1046	21.5	1201340	557.759795	27.2467	761	104.6
13	0x6ed08aa2c822cece9ad916bd6b382a8d24bd1a76add02ac00f6769eee745feff	567	20.5	1110460	518.892355	14.4621	485	56.7
14	0x4af8416c75e6d0b8fa39d006267a5711c85316ea75e9f402aacc1e250e7dd4d1	580	17.7	1035170	476.2235225	23.7931	442	58
15	0xd82fece63d253206e32dae8eb19f942442bbaf90cd82874bb63e1604f4f141c6	1019	85	0	451.35	27.3798	740	101.9
16	0x58384d2e38bc94101586b952c0887d94df28414cc2602b59035cd3d85b00155b	576	20.9	750500	388.101125	13.7153	497	57.6
17	0x3d06b94cc2493b6fc56fb433e4ea37b3b125831e197f50be50d5b8419ed1075d	676	17.3	796590	386.0038575	30.3254	471	67.6
18	0x1d9db0748d575c5421f1c2d18fdd8b025beec0faa9332d397225a8c44f5f83f5	780	19.9	623120	335.75606	23.8462	594	78
19	0x8974d2e2914a37e04a11f3432c90754288de262260196cb2d4d118e886a44099	162	13.5	614200	298.47835	25.3086	121	16.2
20	0x71d6545f5e18bb15bc21ea1ff433b35b7df45ea94bf2f202c77f37e428770fc6	580	3.9	672170	268.9077725	28.7931	413	58

...

2613

267KB

23s

flipside

flipside