with stakers as

(

SELECT

tx_receiver as governor,

COUNT(DISTINCT tx_signer) as number_of_stakers,

sum(deposit/10e24) as staked_amount

FROM near.core.fact_actions_events_function_call c

JOIN near.core.fact_transactions t

ON c.tx_hash = t.tx_hash

WHERE method_name in ('deposit_and_stake')

and deposit/10e24 > 0

GROUP BY governor

HAVING number_of_stakers > 50

order by number_of_stakers desc

),

unstakers as

(

select tx_receiver as governor,

count(distinct tx_signer) as number_of_unstakers,

sum(((REGEXP_SUBSTR(logs[0], '[0-9]+'))::int)/10e24) as unstaked_amount

from near.core.fact_actions_events_function_call c inner join near.core.fact_transactions t

on c.tx_hash=t.tx_hash

inner join near.core.fact_receipts r

on c.tx_hash=r.tx_hash

where c.method_name in ('unstake_all','unstake')

GROUP BY governor

HAVING number_of_unstakers > 50 and unstaked_amount>0

order by number_of_unstakers desc

)

select stakers.governor,

stakers.number_of_stakers,

stakers.staked_amount,

unstakers.number_of_unstakers,

unstakers.unstaked_amount,