with

pcs as (

SELECT

date(recorded_at) as date,

symbol,

address,

avg(price) AS token_price

FROM

osmosis.core.dim_prices,

osmosis.core.dim_tokens

where

symbol = project_name

group BY

1,

2,

3

),

fin as (

select

y.*,symbol,

parse_json(attribute_value):"amount" / pow(10, decimal) * token_price as AMOUNT_usd

FROM

osmosis.core.fact_msg_attributes a

JOIN osmosis.core.fact_transfers y ON a.tx_id = y.tx_id

JOIN pcs p ON p.address = y.currency

AND date = trunc(a.block_timestamp, 'day')

WHERE

a.attribute_key = 'packet_data'

),

final as (

select

*,

regexp_substr(sender, '[a-zA-Z]+|\d+') as source_chain,

regexp_substr(receiver, '[a-zA-Z]+|\d+') as Destination_chain,