WITH delegate AS (
select
delegator_address,
validator_address,
validator_address_name,
validator_address_label,
validator_label_subtype,
validator_label_type,
sum(event_amount) AS delegated
from terra.staking
where action <> 'undelegate'
group by delegator_address, validator_address, validator_address_name, validator_address_label, validator_label_subtype, validator_label_type
),
topDelegators AS (
select
delegator_address,
sum(event_amount) AS delegated
from terra.staking
where delegator_address <> 'terra1dp0taj85ruc299rkdvzp4z5pfg6z6swaed74e6'
group by delegator_address
-- limit 25
),
top25 AS (
select
row_number() over (order by delegated desc) AS rank,
delegator_address,
delegated
from topDelegators
qualify rank < 26
),
mQuery AS (
select
top25.rank,
delegate.*
from delegate, top25
where delegate.delegator_address = top25.delegator_address