SELECT

DAY,

validator_address,

SUM(token_flow) AS TOKEN_FLOW_TOTAL

from (SELECT

date_trunc('week', block_timestamp) as day,

validator_address,

sum(Decode(action ,'undelegate' , (amount / power(10,6)) * -1 , (amount / power(10,6)))) as token_flow

FROM osmosis.core.fact_staking

where currency LIKE 'uosmo'

AND REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL

GROUP BY 1,2

SELECT

date_trunc('week', block_timestamp) as day,

REDELEGATE_SOURCE_VALIDATOR_ADDRESS as validator_address,

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

FROM osmosis.core.fact_staking

where currency LIKE 'uosmo'

AND NOT REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL

GROUP BY 1,2

GROUP BY 1,2 ),

Q2 as (

SELECT

*,

(sum(token_flow_total) OVER (PARTITION BY validator_address ORDER BY DAY)) / (sum(token_flow_total) OVER (ORDER BY DAY)) as voting_ratio

FROM Q1

ORDER BY 1,4 DESC

), Q3 as (

SELECT

*,