MasiTop Validators based on total staked volume in USD
    Updated 2023-02-23
    with osmo_price as ( select trunc(RECORDED_HOUR,'week') as day,
    'Osmo' as symbol,
    avg(price) as avg_price
    from osmosis.core.ez_prices
    where RECORDED_HOUR >= CURRENT_DATE - 180
    and symbol = 'OSMO'
    group by 1,2)
    ,
    atom_price as ( select trunc(RECORDED_HOUR,'week') as day,
    'Atom' as symbol,
    avg(price) as avg_price
    from osmosis.core.ez_prices
    where RECORDED_HOUR >= CURRENT_DATE - 180
    and symbol = 'ATOM'
    group by 1,2 )
    ,
    osmo_staking as ( select trunc(block_timestamp,'week') as weekly,
    VALIDATOR_ADDRESS,
    count(DISTINCT tx_id) as count_txs,
    count(DISTINCT DELEGATOR_ADDRESS) as count_users,
    sum(amount/pow(10,6)) as total_osmos
    from osmosis.core.fact_staking
    where block_timestamp >= CURRENT_DATE - 180
    group by 1,2)
    ,
    osmo_usd as ( select weekly,
    VALIDATOR_ADDRESS,
    count_txs,
    count_users,
    total_osmos,
    total_osmos*avg_price as osmo_usd
    from osmo_staking a left outer join osmo_price b on a.weekly = b.day )
    ,
    staking as ( select block_timestamp,
    tx_id,
    split(ATTRIBUTE_VALUE,'uatom')[0]::numeric/pow(10,6) as atom_amount
    Run a query to Download Data