from lava.core.fact_msg_attributes

where attribute_key = 'memo'

and msg_type = 'ibc_transfer'

and TRY_PARSE_JSON(attribute_value):destination_chain::STRING = 'arbitrum'

and tx_succeeded = 'TRUE'

and block_timestamp::Date >= '2024-07-31')

,

amount as (select block_timestamp,

tx_id,

TRY_PARSE_JSON(attribute_value):amount::STRING/pow(10,6) AS amount,

TRY_PARSE_JSON(attribute_value):denom::STRING AS denom,

TRY_PARSE_JSON(attribute_value):sender::STRING AS sender,

TRY_PARSE_JSON(TRY_PARSE_JSON(attribute_value):memo::STRING):destination_chain::STRING AS destination_chain

from lava.core.fact_msg_attributes

where TRY_PARSE_JSON(TRY_PARSE_JSON(attribute_value):memo::STRING):destination_chain::STRING = 'arbitrum'

and ATTRIBUTE_KEY = 'packet_data'

and MSG_TYPE = 'send_packet'

and tx_id in (select tx_id from transaction))

,

validators as (select DISTINCT validator_address

from lava.gov.fact_staking_rewards)

,

vali_address as (select validator_address,

LEFT(validator_address, POSITION('@' IN validator_address) - 1),

'@',

SUBSTRING(

validator_address,

POSITION('@valoper' IN validator_address) + LENGTH('@valoper'), 30))::string AS transformed_address

from validators)

,

final as ( select block_timestamp,

tx_id,

sender,

case when a.sender iLIKE CONCAT('%', b.transformed_address, '%') then 'Validator' else 'Normal User' end as type,