-- count(*) as total_count,

select

date,

-- value_sender,

avg(VOLUME) as average_of_volume

-- count(distinct value_sender) as active_senders,

-- sum_of_volume/total_count as average_count ,

-- sum_of_volume/active_senders as average_users

from (

select to_date(block_timestamp) as date,

MESSAGE_VALUE['sender'] as value_sender,

MESSAGE_VALUE['receiver'] as value_receiver,

(AMOUNT/1e6) as volume

,

case

when SUBSTR(value_receiver, 0, 4) = 'osmo' then 'osmo'

when SUBSTR(value_receiver, 0, 4) = 'axel' then 'axelar'

when SUBSTR(value_receiver, 0, 4) = 'grav' then 'GRAV'

when SUBSTR(value_receiver, 0, 4) = 'secr' then 'secret'

when SUBSTR(value_receiver, 0, 4) = 'terr' then 'terra'

when SUBSTR(value_receiver, 0, 3) = 'cre' then 'CRE'

when SUBSTR(value_receiver, 0, 3) = 'sif' then 'SIF'

when SUBSTR(value_receiver, 0, 4) = 'kuji' then 'kujira'

when SUBSTR(value_receiver, 0, 4) = 'cosm' then 'cosmos'

when SUBSTR(value_receiver, 0, 4) = 'evmo' then 'evmos'

when SUBSTR(value_receiver, 0, 4) = 'stri' then 'STRI'

when SUBSTR(value_receiver, 0, 4) = 'juno' then 'juno'

else null end as category

from terra.core.ez_transfers

where

MESSAGE_TYPE='/ibc.applications.transfer.v1.MsgTransfer' and CURRENCY='uluna')

group by date

)

select * from final_result;