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))

,

final as (select 'Stake' as type,

user,

sum(amount) as amount,

avg(amount) as avg,

max(amount) as max_amount,

median(amount) as median_amount

from stake_tx

group by 1,2)

select count(DISTINCT user) as users,

case when amount <= 1 then 'a. <= 1 APT'

when amount <= 10 then 'b. 1-10 APT'

when amount <= 25 then 'c. 10-25 APT'

when amount <= 50 then 'd. 25-50 APT'

when amount <= 100 then 'e. 50-100 APT'

when amount <= 250 then 'f. 100-250 APT'