select

redelegate_source_validator_address as validator_address,

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

count(distinct(tx_id)) as tx_number

from osmosis.core.fact_staking

where 1 = 1

and redelegate_source_validator_address is not null

and currency = 'uosmo'

and tx_status = 'SUCCEEDED'

and block_timestamp::date >= current_date - {{ days }}

group by redelegate_source_validator_address

union

select

validator_address,

sum(iff(action = 'undelegate', -1, 1) * (amount / pow(10, 6))) as token_flow,

count(distinct(tx_id)) as tx_number

from osmosis.core.fact_staking

where 1 = 1

and redelegate_source_validator_address is null

and currency = 'uosmo'

and tx_status = 'SUCCEEDED'

and block_timestamp::date >= current_date - {{ days }}

group by validator_address

select

validator_address,

sum (token_flow) as token_flow,

sum(tx_number) as tx_number

from list

group by validator_address

order by token_flow desc, tx_number desc