MadiTop 10 Validators by amount of Staked ATOM on November, 14
    Updated 2022-12-11
    with t1 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'create_validator' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),

    t2 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'create_validator' and ATTRIBUTE_KEY = 'validator' and TX_SUCCEEDED = 'TRUE'),

    t3 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount
    from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'delegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
    t4 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'delegate' and ATTRIBUTE_KEY = 'validator' and TX_SUCCEEDED = 'TRUE'),

    t5 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount
    from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
    t6 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'destination_validator' and TX_SUCCEEDED = 'TRUE'),

    t7 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6)*-1 as amount
    from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
    t8 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
    where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'source_validator' and TX_SUCCEEDED = 'TRUE'),

    income1 as (select t1.date, validator, amount from t1 join t2 on t1.tx_id = t2.tx_id),
    income2 as (select t3.date, validator, amount from t3 join t4 on t3.tx_id = t4.tx_id),
    income3 as (select t5.date, validator, amount from t5 join t6 on t5.tx_id = t6.tx_id),
    outcome as (select t7.date, validator, amount from t7 join t8 on t7.tx_id = t8.tx_id),

    balance as (
    select date, validator, sum(amount) as amount_atom from (
    select * from income1 UNION ALL select * from income2 UNION ALL select * from income3 UNION ALL select * from outcome)
    group by 1,2)
    Run a query to Download Data