total_staked_table as (

SELECT

*,

REDELEGATE_SOURCE_VALIDATOR_ADDRESS as node_validator,

-(amount / power(10,decimal))as staked

FROM osmosis.core.fact_staking

where REDELEGATE_SOURCE_VALIDATOR_ADDRESS is not null

union

SELECT

*,

validator_address as node_validator,

CASE WHEN action = 'undelegate' then -(amount / power(10,decimal))

else amount/power(10,6) end as staked

FROM osmosis.core.fact_staking

where REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL

union

SELECT

*,

validator_address as node_validator,

(amount / power(10,decimal)) as staked

FROM osmosis.core.fact_staking

where REDELEGATE_SOURCE_VALIDATOR_ADDRESS is not null

),daily as (

SELECT

trunc(block_timestamp,'day') as days,

tx_id,

node_validator,

sum(staked) as osmo_staked

FROM total_staked_table WHERE staked is not null group by 1,2,3

),

totals as (

SELECT

days,

sum(osmo_staked) as month_osmo_staked,

sum(month_osmo_staked) over (order by days) as total_osmo_staked