propbase | aureasarsanedes

propbase

aureasarsanedespropbase

Updated 4 days ago

WITH base_data AS (

SELECT

tx_hash,

block_timestamp,

account_address as wallet,

event_resource,

case when event_data:amount is not null then event_data:amount/pow(10,8) else event_data:claimed_amount/pow(10,8) end as props_amount,

DATE_TRUNC('week', block_timestamp) as week_start

FROM aptos.core.fact_events

WHERE payload_function ilike '%propbase_staking%'

AND event_module = 'propbase_staking'

AND event_resource IN ('StakeEvent','UnStakeEvent','ClaimPrincipalAndRewardEvent')

weekly_metrics AS (

SELECT

week_start,

COUNT(DISTINCT CASE WHEN event_resource = 'StakeEvent' THEN tx_hash END) as stake_tx_count,

COUNT(DISTINCT CASE WHEN event_resource in ('UnStakeEvent','ClaimPrincipalAndRewardEvent') THEN tx_hash END) as unstake_tx_count,

COUNT(DISTINCT wallet) as total_unique_wallets,

COUNT(DISTINCT CASE WHEN event_resource = 'StakeEvent' THEN wallet END) as staking_wallets,

COUNT(DISTINCT CASE WHEN event_resource in ('UnStakeEvent','ClaimPrincipalAndRewardEvent') THEN wallet END) as unstaking_wallets,

SUM(CASE WHEN event_resource = 'StakeEvent' THEN props_amount ELSE 0 END) as weekly_staked,

SUM(CASE WHEN event_resource in ('UnStakeEvent','ClaimPrincipalAndRewardEvent') THEN props_amount ELSE 0 END) as weekly_unstaked,

AVG(CASE WHEN event_resource = 'StakeEvent' THEN props_amount END) as avg_stake_size,

AVG(CASE WHEN event_resource in ('UnStakeEvent','ClaimPrincipalAndRewardEvent') THEN props_amount END) as avg_unstake_size

FROM base_data

GROUP BY week_start

cumulative_metrics AS (

SELECT

week_start,

stake_tx_count,

unstake_tx_count,

total_unique_wallets,

Last run: 4 days ago

	WEEK_START	STAKE_TX_COUNT	UNSTAKE_TX_COUNT	TOTAL_TX_COUNT	TOTAL_UNIQUE_WALLETS	STAKING_WALLETS	UNSTAKING_WALLETS	WEEKLY_STAKED	WEEKLY_UNSTAKED	NET_WEEKLY_CHANGE	AVG_STAKE_SIZE	AVG_UNSTAKE_SIZE	CUMULATIVE_STAKED	CUMULATIVE_UNSTAKED	NET_STAKED_CUMULATIVE	PROPS_PRICE	NET_STAKED_CUMULATIVE_USD
1	2025-04-14 00:00:00.000	39	6	45	31	28	3	471805.22000001	220609.3061374	251195.91386261	12097.56974359	36768.217689567	369133667.980004	257790395.687193	111343272.292811	0.02615705	2912411.54052667
2	2025-04-07 00:00:00.000	79	18	97	50	44	7	5874326.73000005	1598198.21720888	4276128.51279117	74358.566202532	88788.789844938	368661862.760004	257569786.381055	111092076.378948	0.02662006	2957277.73873219
3	2025-03-31 00:00:00.000	92	20	112	66	61	9	8080652.37000008	2345085.86372091	5735566.50627917	87833.177934783	117254.293186046	362787536.030003	255971588.163846	106815947.866157	0.0228697	2442848.68291466
4	2025-03-24 00:00:00.000	70	17	87	45	41	4	1875332.29000006	3133221.76677098	-1257889.47677092	26790.461285715	184307.162751234	354706883.660003	253626502.300125	101080381.359878	0.02626673	2655051.08547695
5	2025-03-17 00:00:00.000	44	15	59	40	34	6	1164756.02000001	465062.05311683	699693.96688318	26471.727727273	31004.136874455	352831551.370003	250493280.533354	102338270.836649	0.02222048	2274005.50036034
6	2025-03-10 00:00:00.000	56	12	68	45	40	5	655721.12750006	356752.16559118	298968.96190888	11709.305848215	29729.347132598	351666795.350003	250028218.480238	101638576.869766	0.02135749	2170744.88911025
7	2025-03-03 00:00:00.000	50	24	74	40	34	7	1996071.38	2248509.51374476	-252438.13374476	39921.4276	93687.896406032	351011074.222503	249671466.314646	101339607.907857	0.02282813	2313393.74346958
8	2025-02-24 00:00:00.000	147	54	201	81	78	8	10859233.9600001	5893998.33467041	4965235.62532969	73872.339863946	109148.117308711	349015002.842503	247422956.800902	101592046.041602	0.0325514	3306963.32751859
9	2025-02-17 00:00:00.000	92	59	151	72	65	9	17864958.61	16385816.8078836	1479141.80211642	194184.332717392	277725.708608197	338155768.882503	241528958.466231	96626810.416272	0.03522473	3403653.30767437
10	2025-02-10 00:00:00.000	84	66	150	66	60	8	9807463.78000007	6909995.10566444	2897468.67433563	116755.521190477	104696.89554037	320290810.272503	225143141.658348	95147668.6141555	0.0340853	3243136.82901408
11	2025-02-03 00:00:00.000	94	76	170	60	49	12	2685101.31000003	8101843.43499795	-5416742.12499792	28564.907553192	106603.203092078	310483346.492503	218233146.552683	92250199.9398199	0.03626296	3345265.31040969
12	2025-01-27 00:00:00.000	35	12	47	26	20	7	524259.69000002	440785.08302113	83474.60697889	14978.848285715	36732.090251761	307798245.182503	210131303.117685	97666942.0648178	0.04132173	4035767.00992804
13	2025-01-20 00:00:00.000	63	18	81	52	44	11	2343613.89000002	865120.71792521	1478493.17207481	37200.220476191	48062.262106956	307273985.492503	209690518.034664	97583467.4578389	0.050607	4938406.53763885
14	2025-01-13 00:00:00.000	69	42	111	39	29	11	3174142.83500003	975343.16940591	2198799.66559412	46002.070072464	23222.456414426	304930371.602503	208825397.316739	96104974.2857641	0.05560219844	5343647.85095768
15	2025-01-06 00:00:00.000	68	35	103	50	41	9	1408788.96000003	789284.68994532	619504.27005471	20717.484705883	22550.991141295	301756228.767503	207850054.147333	93906174.62017	0.06762495381	6350400.7211664
16	2024-12-30 00:00:00.000	111	82	193	72	63	11	2460342.59000004	4479158.74651435	-2018816.15651431	22165.248558559	54623.887152614	300347439.807503	207060769.457388	93286670.3501153	0.08092363578	7549096.53492122
17	2024-12-23 00:00:00.000	110	154	264	63	55	10	5774941.49000004	12096776.1033085	-6321834.61330849	52499.468090909	78550.494177328	297887097.217503	202581610.710873	95305486.5066296	0.08299592425	7909966.93848123
18	2024-12-16 00:00:00.000	114	30	144	55	48	8	2364726.26000011	3230320.88933799	-865594.629337881	20743.212807019	107677.362977933	292112155.727503	190484834.607565	101627321.119938	0.09197593071	9347267.44583099
19	2024-12-09 00:00:00.000	96	29	125	58	52	9	1378599.60000006	730791.98347222	647807.61652784	14360.412500001	25199.723568008	289747429.467503	187254513.718227	102492915.749276	0.1097742999	11251088.0761679
20	2024-12-02 00:00:00.000	116	55	171	77	63	16	1377567.21500004	4803720.57922598	-3426153.36422594	11875.579439656	87340.374167745	288368829.867503	186523721.734755	101845108.132748	0.1276060421	12996051.1520856

13KB

12s

flipside

flipside