sum(amount/pow(10,6)) as delegate_amount,

count(DISTINCT tx_id) as delegate_tx,

count(DISTINCT DELEGATOR_ADDRESS) as delegate_user,

avg(amount/pow(10,6)) as avg_delegate_amount

from axelar.gov.fact_staking

where action = 'delegate'

group by 1

,

undelegate as (select DELEGATOR_ADDRESS,

sum(amount/pow(10,6))*-1 as undelegate_amounts,

count(DISTINCT tx_id)*-1 as undelegate_txs,

count(DISTINCT DELEGATOR_ADDRESS)*-1 as undelegate_user,

avg(amount/pow(10,6)) as avg_undelegate_amounts

from axelar.gov.fact_staking

where action = 'undelegate'

group by 1

select a.DELEGATOR_ADDRESS,

delegate_amount,

ifnull(avg_undelegate_amounts,0) as undelegate_amount,

delegate_amount+undelegate_amount as Net,

delegate_tx,

ifnull(undelegate_txs,0) as undelegate_tx,

avg_delegate_amount,

ifnull(avg_undelegate_amounts,0) as avgavg_undelegate_amount

from delegate a left outer join undelegate b on a.DELEGATOR_ADDRESS = b.DELEGATOR_ADDRESS

order by 4 desc

limit 1000