select

date_trunc('day',recorded_at) as p_date,

lower(symbol) as symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

group by 1,2

,bridge as (

SELECT

BLOCK_TIMESTAMP,

tx_id,

sender,

lower(split(currency,'-')[0]) as symbol,

iff(symbol ilike 'u%', substring(symbol, 2, LEN(symbol)), symbol) as asset,

(amount/pow(10,decimal)) as adjusted_amt

from axelar.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

AND not CURRENCY ilike any ('factory%', 'gravity%')

AND amount is not NULL

AND currency is not NULL

)

date_trunc('day', BLOCK_TIMESTAMP)::date as date,

asset,

COUNT(DISTINCT tx_id) as bridge_cnt,

COUNT(DISTINCT sender) as bridger_cnt,

sum(adjusted_amt * b.price_usd) as amt

FROM bridge a left join priceTb b on (a.BLOCK_TIMESTAMP::date = b.p_date and a.asset = b.symbol)

WHERE date BETWEEN '2022-11-05' and '2022-11-18'

GROUP by 1,2

order by 1