select

date(RECORDED_HOUR) as day,

upper(symbol) as symbol,

avg(close) as usd

from

crosschain.core.fact_hourly_prices a join crosschain.core.dim_asset_metadata b on a.id=b.id

GROUP BY 1,2

), trans as (

select

TX_ID,

initcap(replace(attribute_value, '"', '')) as chain

axelar.core.fact_msg_attributes

where attribute_key='chain' and TX_SUCCEEDED and attribute_value is not null

), senders as (

select

a.TX_ID,

attribute_value as sender

from axelar.core.fact_msg_attributes a

join trans b on a.tx_id = b.tx_id

where attribute_key='sender' and msg_type='transfer' -- and attribute_index = 1

), bridges as (

select

date(block_timestamp) as day,

s.sender as wallet,

CASE

WHEN attribute_value LIKE '%uaxl%' THEN cast(REPLACE(attribute_value, 'uaxl', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%uusdc%' THEN cast(REPLACE(attribute_value, 'uusdc', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' THEN cast(REPLACE(attribute_value, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' THEN cast(REPLACE(attribute_value, 'ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%weth-wei%' THEN cast(REPLACE(attribute_value, 'weth-wei', '') as INT) / power(10, 18)

WHEN attribute_value LIKE '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' THEN cast(REPLACE(attribute_value, 'ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%wbtc-satoshi%' THEN cast(REPLACE(attribute_value, 'wbtc-satoshi', '') as INT) / power(10, 9)

END as amount,

CASE