WITH axl_price AS (
SELECT
trunc(hour, 'day') AS Time,
AVG(price) AS usd_price
FROM crosschain.price.ez_hourly_token_prices
WHERE symbol = 'AXL'
GROUP BY 1
)
SELECT
COUNT(DISTINCT date_trunc('day', block_timestamp)) AS date,
COUNT(DISTINCT tx_hash) AS Transactions,
COUNT(DISTINCT sender) AS Users,
SUM(amount * axl.usd_price) AS usd_volume,
COUNT(DISTINCT tx_hash) / COUNT(DISTINCT sender) AS avg_tx_per_user,
SUM(amount * axl.usd_price) / COUNT(DISTINCT date_trunc('day', block_timestamp)) AS avg_vol_per_day
FROM
axelar.defi.ez_bridge_satellite
JOIN
axl_price axl ON date_trunc('day', block_timestamp) = axl.Time