SELECT

tx.block_timestamp,

tx.sender,

CASE

WHEN ev.EVENT_RESOURCE = 'MintEvent' THEN ev.EVENT_DATA:amapt::FLOAT / POW(10, 8)

WHEN ev.EVENT_RESOURCE = 'WithdrawEvent' THEN -1 * ev.EVENT_DATA:amount::FLOAT / POW(10, 8)

ELSE 0

END AS stake_amount,

tx.sender AS staker

FROM aptos.core.fact_events ev

JOIN aptos.core.fact_transactions tx

ON ev.tx_hash = tx.tx_hash

AND ev.block_timestamp = tx.block_timestamp

WHERE ev.EVENT_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a'

AND ev.EVENT_RESOURCE IN ('MintEvent', 'WithdrawEvent')

AND tx.SUCCESS = 'TRUE'

),

aggregated AS (

SELECT

SUM(CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 HOURS' THEN stake_amount ELSE 0 END) AS Net_staked_Amt_24H,

COUNT(DISTINCT CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 HOURS' THEN staker END) AS N_Stakers_24H,

SUM(CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS' THEN stake_amount ELSE 0 END) AS Net_staked_Amt_7D,

COUNT(DISTINCT CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS' THEN staker END) AS N_Stakers_7D,

SUM(CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS' THEN stake_amount ELSE 0 END) AS Net_staked_Amt_30D,

COUNT(DISTINCT CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS' THEN staker END) AS N_Stakers_30D

FROM time_frames

SELECT * FROM aggregated;