SELECT

date_trunc('week', block_timestamp) as date,

validator_address,

sum(CASE WHEN action LIKE 'undelegate' THEN (amount / power(10,6)) * -1

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

FROM osmosis.core.fact_staking

where currency LIKE 'uosmo'

AND REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL

GROUP BY 1,2

), taula2 as (

SELECT

date_trunc('week', block_timestamp) as date,

REDELEGATE_SOURCE_VALIDATOR_ADDRESS as validator_address,

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

FROM osmosis.core.fact_staking

where currency LIKE 'uosmo'

AND NOT REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL

GROUP BY 1,2

), taula3 AS (

SELECT *

FROM taula1

UNION

SELECT *

FROM taula2

), taula4 as (

SELECT

date,

validator_address,

SUM(volume) AS total_volume

FROM taula3

GROUP BY 1,2