tx_hash,

signer_id as user,

ARGS:"amount"/pow(10,18) as amount,

from near.core.fact_actions_events_function_call

where ARGS:"receiver_id" = 'xtoken.ref-finance.near'

and method_name = 'ft_transfer_call'

and RECEIVER_ID = 'token.v2.ref-finance.near'

and ARGS:"amount"/pow(10,18) < 1e8

and RECEIPT_SUCCEEDED != 'FALSE'

,

unstake as ( select block_timestamp,

tx_hash,

signer_id as user,

ARGS:"amount"/pow(10,18) as amount

from near.core.fact_actions_events_function_call

where method_name = 'callback_post_unstake'

and RECEIVER_ID = 'xtoken.ref-finance.near'

and ARGS:"amount"/pow(10,18) < 1e8

and RECEIPT_SUCCEEDED != 'FALSE'

,

stake_total as ( select trunc(block_timestamp,'month') as monthly,

count(DISTINCT user) as stakers,

count(DISTINCT tx_hash) as stake_tx,

sum(amount) as staked_amount,

sum(staked_amount) over (order by monthly asc) as cum_stake,

avg(amount) as avg_stake_amount

from stake

group by 1)

,

unstake_total as ( select trunc(block_timestamp,'month') as monthly,

count(DISTINCT user)*-1 as unstakers,

count(DISTINCT tx_hash) as unstake_tx,