prices as (

SELECT

RECORDED_AT::date as date,

symbol,

avg(price) as price

FROM osmosis.core.dim_prices

GROUP BY 1, 2

,

raw as (

SELECT

BLOCK_TIMESTAMP,

CASE WHEN STARTSWITH(LEFT(SENDER, POSITION('1' IN SENDER) - 1), '0x') THEN NULL ELSE INITCAP(LEFT(SENDER, POSITION('1' IN SENDER) - 1), ' ') END as sender_network,

CASE WHEN STARTSWITH(LEFT(RECEIVER, POSITION('1' IN RECEIVER) - 1), '0x') THEN NULL ELSE INITCAP(LEFT(RECEIVER, POSITION('1' IN RECEIVER) - 1), ' ') END as receiver_network,

TX_ID,

SENDER,

RECEIVER,

UPPER(iff(lower(split(currency,'-')[0]) ilike 'u%',substring(lower(split(currency,'-')[0]),2,LEN(lower(split(currency,'-')[0]))), lower(split(currency,'-')[0]))) as asset,

AMOUNT / POW(10, DECIMAL) as amount_adj

FROM axelar.core.fact_transfers f

WHERE 1=1

AND TX_SUCCEEDED = TRUE

AND receiver_network ILIKE 'osmo'

AND TRANSFER_TYPE = 'IBC_TRANSFER_OUT'

,

raw_priced as (

SELECT

BLOCK_TIMESTAMP,

sender_network,

receiver_network,

TX_ID,

SENDER,

RECEIVER,

asset,