WITH axl_price AS (
SELECT
trunc(recorded_hour, 'week') AS Time,
AVG(price) AS usd_price
FROM osmosis.price.ez_prices
WHERE symbol = 'AXL'
GROUP BY 1
)
SELECT
date_trunc('day', block_timestamp) AS date,
COUNT(DISTINCT tx_hash) AS Transactions,
COUNT(DISTINCT sender) AS Users,
amount * axl.usd_price AS usd_volume,
COUNT(DISTINCT tx_hash) / COUNT(DISTINCT sender) AS avg_tx_per_user
FROM
axelar.defi.ez_bridge_squid
JOIN
axl_price axl ON date_trunc('day', block_timestamp) = axl.Time
GROUP BY 1,4
ORDER BY 1