flipside

    Validators Count & Weight (Avalanche Monitor)
    Ali3NValidators Count & Weight (Avalanche Monitor)
    Updated 2025-03-16
    with raw_valid_count as (
    SELECT livequery.live.udf_api(concat(
    'https://metrics.avax.network/v1/validators/network/mainnet/subnetId/11111111111111111111111111111111LpoYY/count
    ?from=',to_char(DATE_PART(epoch_second, current_timestamp()) - 31536000),
    '&to=',DATE_PART(epoch_second, current_timestamp()) :: string,
    '&interval=day')) as raw_response),

    valid_count as (
    select value:timestamp as timestamp,
    to_timestamp(timestamp) as date_time,
    value:value as validators_count
    from raw_valid_count,
    LATERAL FLATTEN (input => raw_response:data:results)),

    raw_valid_weight as (
    SELECT livequery.live.udf_api(concat(
    'https://metrics.avax.network/v1/validators/network/mainnet/subnetId/11111111111111111111111111111111LpoYY/weight
    ?from=',to_char(DATE_PART(epoch_second, current_timestamp()) - 31536000),
    '&to=', DATE_PART(epoch_second, current_timestamp()) :: string,
    '&interval=day')) as raw_response),

    valid_weight as (
    select value:timestamp as timestamp,
    to_timestamp(timestamp) as date_time,
    value:value*pow(10,-9) as validator_weights
    from raw_valid_weight,
    LATERAL FLATTEN (input => raw_response:data:results))


    select coalesce (t1.date_time,t2.date_time) as date,
    case when t1.date_time <= '2024-12-16 17:00:00.000' then 'Before ETNA'
    else 'After ETNA' end as timespan,
    validators_count,
    validator_weights
    from valid_count t1 full outer join valid_weight t2 on t1.date_time = t2.date_time
    order by 1 desc
    Last run: 22 days ago
    DATE
    TIMESPAN
    VALIDATORS_COUNT
    VALIDATOR_WEIGHTS
    1
    		2025-03-16 00:00:00.000After ETNA
    266
    		71257509.7530506
    2
    		2025-03-15 00:00:00.000After ETNA
    272
    		71305957.7530506
    3
    		2025-03-14 00:00:00.000After ETNA
    274
    		71525616.7778806
    4
    		2025-03-13 00:00:00.000After ETNA
    278
    		71533616.7778806
    5
    		2025-03-12 00:00:00.000After ETNA
    279
    		71586331.9126191
    6
    		2025-03-11 00:00:00.000After ETNA
    282
    		73153717.4126191
    7
    		2025-03-10 00:00:00.000After ETNA
    282
    		73153717.4126191
    8
    		2025-03-09 00:00:00.000After ETNA
    289
    		73167719.4126191
    9
    		2025-03-08 00:00:00.000After ETNA
    291
    		73192244.7474121
    10
    		2025-03-07 00:00:00.000After ETNA
    294
    		73198819.7472738
    11
    		2025-03-06 00:00:00.000After ETNA
    295
    		73288819.7672738
    12
    		2025-03-05 00:00:00.000After ETNA
    295
    		73288819.7672738
    13
    		2025-03-04 00:00:00.000After ETNA
    298
    		73294819.7672738
    14
    		2025-03-03 00:00:00.000After ETNA
    303
    		73304819.7677174
    15
    		2025-03-02 00:00:00.000After ETNA
    308
    		73319874.147499
    16
    		2025-03-01 00:00:00.000After ETNA
    316
    		73391379.145499
    17
    		2025-02-28 00:00:00.000After ETNA
    322
    		75912214.149612
    18
    		2025-02-27 00:00:00.000After ETNA
    327
    		75943163.149612
    19
    		2025-02-26 00:00:00.000After ETNA
    337
    		75981164.599612
    20
    		2025-02-25 00:00:00.000After ETNA
    339
    		78983164.599612
    100
    6KB
    2s