with stake as ( select block_timestamp, tx_hash, signer_id as user, ARGS:"amount"/pow(10,18) as amount, args 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, sum(amount)*-1 as unstaked_amount, sum(unstaked_amount) over (order by monthly asc) as cum_unstake, avg(amount) as avg_unstake_amount from unstake group by 1) , breakdown as ( select user, sum(amount) as staked_amount from stake group by 1) select count(DISTINCT user) as users, case when staked_amount <= 10 then '<= 10 REF' when staked_amount <= 100 then '10-100 REF' when staked_amount <= 500 then '100-500 REF' when staked_amount <= 1000 then '500-1K REF' when staked_amount <= 5000 then '1K-5K REF' when staked_amount <= 10000 then '5K-10K REF' when staked_amount <= 50000 then '10K-50K REF' when staked_amount <= 100000 then '50K-100K REF' when staked_amount <= 500000 then '100K-500K REF' when staked_amount > 500000 then '> 500K REF' end as breakdown from breakdown group by 2 order by 1 desc

$REF Staking!