SELECT avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'MATIC'

ORDER BY hour DESC

LIMIT 1

), bnb_price AS

SELECT avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'BNB'

ORDER BY hour DESC

LIMIT 1

), avax_price AS

SELECT avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'WAVAX'

ORDER BY hour DESC

LIMIT 1

), summary_matic AS

SELECT trn.block_timestamp::date AS date

, COUNT(DISTINCT receiver) AS count_receiver

, COUNT(DISTINCT tx_id) AS count_tx

, SUM(trn.amount / pow(10, decimal)) AS vol_matic

, SUM(trn.amount / pow(10, decimal) * (SELECT price FROM matic_price)) AS vol_usd

FROM osmosis.core.fact_transfers trn

JOIN osmosis.core.dim_labels lbl ON trn.currency = lbl.address

WHERE trn.sender LIKE 'axelar%' -- from

AND trn.receiver LIKE 'osmo%' -- to

AND lbl.project_name = 'wMATIC'

GROUP BY 1

), summary_bnb AS