SELECT

block_timestamp

, tx_hash

, from_address as wallet

from ronin.core.fact_transactions

WHERE 1=1

AND nonce=0

AND tx_succeeded

, stake_stats as (

SELECT

wallet

, COUNT(DISTINCT tx_hash) as tx_counts

, SUM(CASE WHEN action IN ('stake', 'delegate') THEN amount ELSE amount*-1 END) as net_stake

from community.ronin.staking_actions

WHERE 1=1

AND action IN ('delegate', 'undelegate', 'stake', 'unstake')

GROUP BY 1

, merge as (

SELECT

n.*

, coalesce(tx_counts, 0) as tx_count

, coalesce(net_stake, 0) as net_stakes

, CASE WHEN tx_count=0 THEN 'no participation' ELSE 'participated' END as label1

, CASE WHEN net_stakes<=0 THEN 'not currently staking' ELSE 'active staker' END as label2

--from new_users

--LEFT JOIN stake_stats using(wallet)

from new_users n

LEFT JOIN stake_stats s ON n.wallet = s.wallet

SELECT