WITH bnb_price AS

SELECT avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'BNB'

ORDER BY hour DESC

LIMIT 1

), summary 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_bnb

, SUM(trn.amount / pow(10, decimal) * (SELECT price FROM bnb_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 = 'wBNB'

GROUP BY 1

CASE WHEN date > '2022-11-09' THEN 'Incentivized wBNB' ELSE 'Before Incentivized wBNB' END AS Incentivized_type

, avg(count_receiver) AS average_active_user

, avg(count_tx) AS average_tx

, avg(vol_bnb) AS average_vol_bnb

, avg(vol_usd) AS average_vol_usd

FROM summary

GROUP BY 1