ampricte AS (

SELECT price as price

FROM aptos.price.ez_prices_hourly

WHERE token_address = '0x1::aptos_coin::AptosCoin'

ORDER BY hour DESC

LIMIT 1),

stpricte AS (

SELECT price as stapt_price

FROM aptos.price.ez_prices_hourly

WHERE token_address = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt'

ORDER BY hour DESC

LIMIT 1),

staking_events AS (

SELECT

event_resource,

CAST(event_data:amapt AS DOUBLE) / 1e8 as amapt_amount,

CAST(event_data:stapt AS DOUBLE) / 1e8 as stapt_amount

FROM aptos.core.fact_events

WHERE event_address = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a'

AND event_resource IN ('StakeEvent', 'UnstakeEvent', 'MintEvent')

AND success = TRUE

SELECT

-- AMAPT Metrics

SUM(CASE WHEN event_resource = 'StakeEvent' THEN amapt_amount ELSE 0 END) as total_amapt_staked,

SUM(CASE WHEN event_resource = 'UnstakeEvent' THEN amapt_amount ELSE 0 END) as total_amapt_unstaked,

SUM(CASE WHEN event_resource = 'MintEvent' THEN amapt_amount ELSE 0 END) as total_amapt_minted,

SUM(CASE WHEN event_resource = 'StakeEvent' THEN amapt_amount ELSE 0 END) -

SUM(CASE WHEN event_resource = 'UnstakeEvent' THEN amapt_amount ELSE 0 END) as net_amapt_staked,

SUM(CASE WHEN event_resource = 'StakeEvent' THEN amapt_amount * price ELSE 0 END) -

SUM(CASE WHEN event_resource = 'UnstakeEvent' THEN amapt_amount * price ELSE 0 END) as net_amapt_staked_usd,