SELECT

decoded_log:owner::STRING AS user_address,

decoded_log:assets::NUMBER / 1e18 AS unstake_amount,

(SELECT SUM(decoded_log:assets::NUMBER / 1e18)

FROM avalanche.core.fact_decoded_event_logs d

WHERE d.decoded_log:owner::STRING = e.decoded_log:owner::STRING

AND d.EVENT_NAME = 'Deposit') AS total_ever_staked

FROM avalanche.core.fact_decoded_event_logs e

WHERE contract_address = LOWER('0x06d47f3fb376649c3a9dafe069b3d6e35572219e')

AND EVENT_NAME = 'Withdraw'

CASE

WHEN unstake_amount >= total_ever_staked * 0.95 THEN 'Full Exit'

WHEN unstake_amount >= total_ever_staked * 0.5 THEN 'Major Withdrawal'

ELSE 'Partial Rebalance'

END AS unstake_type,

COUNT(*) AS events,

AVG(unstake_amount) AS avg_amount

FROM unstake_events

GROUP BY 1