select

block_timestamp::date as day,

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 day, redelegate_source_validator_address

union

select

block_timestamp::date as day,

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 day, validator_address

select day,

validator_address,

sum(token_flow) as total_token_flow,

sum(tx_number) as total_tx_number,

row_number() over (partition by day order by total_token_flow desc) as day_rank

from list