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,

RECEIVER,

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

) ,

senders as (select

DISTINCT sender,

COUNT(*) as Bridges_cnt

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

group by 1)

, receivers as (select

DISTINCT receiver,

COUNT(*) as Bridges_cnt

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

group by 1),

common as (select

sender as "Common Users",

a.Bridges_cnt as "# of Origin of Bridge Txs",