SELECT

hour,

token_address,

symbol,

decimals,

AVG(price) AS usd_amount

FROM crosschain.price.ez_prices_hourly

GROUP BY hour, token_address, symbol, decimals

CONCAT(SOURCE_CHAIN, ' -> ', DESTINATION_CHAIN) AS chain_route,

COUNT(DISTINCT tx_hash) AS transaction_count,

COUNT(DISTINCT sender) AS unique_sender_count,

SUM(amount * usd_amount) AS transaction_volume,

COUNT(DISTINCT tx_hash) / COUNT(DISTINCT sender) AS average_transactions_per_user,

SUM(amount * usd_amount) / COUNT(DISTINCT sender) AS average_volume_per_user,

SUM(amount * usd_amount) / COUNT(DISTINCT tx_hash) AS average_volume_per_transaction

FROM

axelar.defi.ez_bridge_satellite t1

JOIN price t2

ON date_trunc('hour', t1.block_timestamp) = t2.hour

AND t1.token_address = t2.token_address

WHERE

block_timestamp::date >= '2024-01-01'

GROUP BY

chain_route

HAVING transaction_count > 100;