*,

SPLIT(attribute_value, ',') as tokens,

logs.value as token,

case when REGEXP_SUBSTR(attribute_value,'i.*') is not null then REGEXP_SUBSTR(attribute_value,'i.*')

else REGEXP_SUBSTR(attribute_value,'u.*') end as attribute_name,

REPLACE(attribute_value, attribute_name, '')::decimal/pow(10,6) as amount

FROM lava.core_testnet.fact_msg_attributes

join lateral flatten (input => tokens) logs

WHERE MSG_TYPE = 'lava_delegator_claim_rewards'

AND ATTRIBUTE_KEY = 'claimed'

),

t2 as (select

BLOCK_TIMESTAMP,

TX_ID,

TOKEN,

COALESCE(REGEXP_SUBSTR(TOKEN,'i.*'),REGEXP_SUBSTR(TOKEN,'u.*')) as token_address,

case

when token_address='ibc/2140C190DB0CA3C6EEA3BA0D4A99B7558FC68694137BC9DC8152C10B9205ED05' then 'AXL'

when token_address='ibc/E3FCBEDDBAC500B1BAB90395C7D1E4F33D9B9ECFE82A16ED7D7D141A0152323F' then 'NEAR'

when token_address='ibc/21E6274EDD0A68821E6C2FD4B243DF85EB86FF19920FF35FC18E68939DDE87CB' then 'EVMOS'

when token_address='ibc/77CDF8229441220A39DFD5E38B0AED466227B5DA8434BA8C7A90211A5E85C436' then 'OSMO'

when token_address='ulava' then 'LAVA'

else token_address end as symbol,

REPLACE(TOKEN, token_address, '')::decimal/pow(10,6) as amount

from t1

select

symbol,

sum(amount) as total_volume,

avg(amount) as avg_claim_volume,

count(distinct tx_id) as claim_count

from t2