with delegate as ( select block_timestamp,

tx_id,

msg_group,

attribute_value as validator_address

from lava.core.fact_msg_attributes

where msg_type= 'delegate'

and attribute_key = 'validator'

and tx_succeeded = 'TRUE')

,

delegated_amount as ( select a.block_timestamp,

a.tx_id,

a.msg_group,

validator_address,

splIT(ATTRIBUTE_VALUE,'ulava')[0]/pow(10,6) as amount

from delegate a join lava.core.fact_msg_attributes b on a.tx_id = b.tx_id and a.msg_group = b.msg_group

where msg_type = 'delegate' and attribute_key = 'amount'

and ATTRIBUTE_VALUE ilike '%lava%'

,

delegator as ( select a.block_timestamp,

a.tx_id,

a.msg_group,

validator_address,

amount,

attribute_value as delegator

from delegated_amount a join lava.core.fact_msg_attributes b on a.tx_id = b.tx_id and a.msg_group = b.msg_group

where msg_type = 'delegate'

and attribute_key = 'delegator')

,

source_validators as ( select block_timestamp,

msg_group,

tx_id,

attribute_value as source_validator

from lava.core.fact_msg_attributes