select

address,

delegator_shares,

row_number () over (order by delegator_shares desc) as rn

from cosmos.core.fact_validators

where delegator_shares > 0

),

topvalidators as (

select *

from topvalidatorss

where rn <= 175

),

delegatet as (

select

tx_id,

attribute_value as validator_address

from cosmos.core.fact_msg_attributes

where msg_type = 'delegate'

and attribute_key = 'validator'

and tx_succeeded = 'TRUE'

),

delegateamount as (

select

tx_id,

block_timestamp,

split(ATTRIBUTE_VALUE,'uatom')[0]::numeric/1e6 as Delegate_Amount

from cosmos.core.fact_msg_attributes

where msg_type = 'delegate'

and attribute_key = 'amount'

and tx_id in (select tx_id from delegatet)

),