WEEK_START | POOL_ADDRESS | POOL_NAME | 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 | 0x16cd2c86690aabda8d07050d7e63a177bcf117e8a437e633be871439d8ca08e8 | Past Pools | 29 | 0 | 29 | 21 | 21 | 0 | 256804.22000001 | 0 | 256804.22000001 | 8855.317931035 | 256804.22000001 | 0 | 256804.22000001 | 0.02615705 | 6717.240822751 | |
2 | 2025-01-27 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.04132173 | 619642.219745647 | ||
3 | 2024-12-30 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.08092363578 | 1213494.72316693 | ||
4 | 2024-12-02 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1276060421 | 1913523.25170267 | ||
5 | 2024-11-25 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1229426779 | 1843593.52399938 | ||
6 | 2024-11-18 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.09408879969 | 1410913.6449994 | ||
7 | 2024-11-11 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.09198877598 | 1379422.62678132 | ||
8 | 2024-11-04 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1187490641 | 1780707.96275984 | ||
9 | 2024-10-28 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.09396426115 | 1409046.12062063 | ||
10 | 2024-10-21 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.118754633 | 1780791.47229046 | ||
11 | 2024-10-14 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1359505786 | 2038654.19777647 | ||
12 | 2024-10-07 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1028758165 | 1542679.82774176 | ||
13 | 2024-09-30 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1152108225 | 1727650.07142449 | ||
14 | 2024-09-23 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1300083886 | 1949547.76748465 | ||
15 | 2024-09-16 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.1100236053 | 1649864.87762104 | ||
16 | 2024-09-09 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.08863213901 | 1329087.99701639 | ||
17 | 2024-09-02 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.08290623977 | 1243224.96762514 | ||
18 | 2024-08-26 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.08218696203 | 1232438.99962266 | ||
19 | 2024-08-19 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 0 | 1 | 1 | 2 | 0 | 1 | 0 | 4446.32 | -4446.32 | 4446.32 | 18275090.2450002 | 3279536.58000002 | 14995553.6650001 | 0.05507886363 | 825938.055372804 | |
20 | 2024-08-12 00:00:00.000 | 0x3584c484ebd1e4485f73e55a5874cb75a026455a702e199e091841b05a75f4c4 | Past Pools | 3 | 2 | 5 | 5 | 3 | 2 | 304635.39000001 | 304635.39000001 | 0 | 101545.130000003 | 152317.695000005 | 18275090.2450002 | 3275090.26000002 | 14999999.9850002 | 0.05507886363 | 826182.95362574 |
aureasarsanedespropbase 2
Updated 4 days ago
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
›
⌄
WITH base_data AS (
SELECT
tx_hash,
block_timestamp,
account_address as wallet,
event_resource,
event_address as pool_address, -- Added pool address
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,
pool_address, -- Added pool address
COUNT(DISTINCT CASE WHEN event_resource = 'StakeEvent' THEN tx_hash END) as stake_tx_count,
COUNT(DISTINCT CASE WHEN event_resource = 'UnStakeEvent' 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 = 'UnStakeEvent' 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 = 'UnStakeEvent' 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 = 'UnStakeEvent' THEN props_amount END) as avg_unstake_size
FROM base_data
GROUP BY week_start, pool_address -- Added pool address to GROUP BY
),
cumulative_metrics AS (
SELECT
week_start,
Last run: 4 days ago
...
633
149KB
16s