select date(RECORDED_AT) as pdate, avg(price) as price

from osmosis.core.dim_prices

where symbol = 'AXL'

group by pdate

),

raw as (

SELECT

date_trunc('day', b.block_timestamp) as date1,

CASE WHEN b.attribute_value LIKE '%uaxl%' THEN 'AXL' END as asset_symbol,

a.attribute_value as to_chain,

count(DISTINCT b.tx_id) as tx_count,

sum(case WHEN b.attribute_value LIKE '%uaxl%' THEN cast(REPLACE(b.attribute_value, 'uaxl', '') as INT) / power(10, 6) END) as token_amount

FROM axelar.core.fact_msg_attributes a join axelar.core.fact_msg_attributes b on a.tx_id = b.tx_id and b.msg_type LIKE 'coin_spent' AND b.attribute_index = 1

WHERE a.attribute_key = 'destinationChain'

AND to_chain is not NULL

GROUP BY 1,2,3

SELECT

date_trunc('day', date1) as date,

initcap(to_chain) as chain,

sum(token_amount * price) as volume,

avg((token_amount * price)/ tx_count) as avg_bridge_size,

sum(tx_count) as bridges,

sum(volume) over (partition by chain order by date) as cumu_vol,

sum(bridges) over (partition by chain order by date) as cumu_bridge_count,

row_number() over (partition by date order by volume DESC) as r

FROM raw LEFT JOIN ev_price_tbl ON date1 = pdate

WHERE asset_symbol = 'AXL'

GROUP BY date, to_chain

qualify r<=10