a.block_timestamp,

event_data:"amapt"/pow(10,8) as amount,

sender as user

from aptos.core.fact_events a join aptos.core.fact_transactions b on a.tx_hash = b.tx_hash

and a.event_address like '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a%'

and a.EVENT_RESOURCE in ('MintEvent','DepositEvent','StakeEvent')

and a.tx_hash not in (select tx_hash from aptos.core.fact_events

where event_resource in ('UnstakeEvent','WithdrawalRequestEvent'))

having amount is not null )

,

unstake_tx as ( select DISTINCT a.tx_hash,

a.block_timestamp,

event_data:"amapt"/pow(10,8) as amount,

sender as user

from aptos.core.fact_events a left outer join aptos.core.fact_transactions b on a.tx_hash = b.tx_hash

where event_resource in ('UnstakeEvent')

and a.event_address like '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a%'

and a.tx_hash not in (select tx_hash from stake_tx))

,

stake as ( select

count(DISTINCT tx_hash) as stake_tx,

count(DISTINCT user) as stakers,

sum(amount) as stake_amount,

avg(amount) as avg_stake_amount

from stake_tx

,

unstake as ( select

count(DISTINCT tx_hash) as unstake_txs,

count(DISTINCT user)*-1 as unstaker,

sum(amount)*-1 as unstakes_apt,

avg(amount) as avg_unstakes_amount

from unstake_tx)

select stake_amount,