-- Aptos daily fees with price

SELECT

'aptos' as blockchain,

DATE_TRUNC('day', t.block_timestamp) as date,

AVG(t.gas_used * t.gas_unit_price * p.price)/pow(10,8) as avg_tx_fee_usd

FROM aptos.core.fact_transactions t

LEFT JOIN aptos.price.ez_prices_hourly p

ON DATE_TRUNC('hour', t.block_timestamp) = p.hour

AND p.symbol = 'APT'

WHERE t.block_timestamp >= CURRENT_DATE - INTERVAL '{{days}} days'

AND t.success = TRUE

GROUP BY 1, 2

UNION ALL

-- Ethereum daily fees with price

SELECT

'ethereum' as blockchain,

DATE_TRUNC('day', t.block_timestamp) as date,

AVG(t.tx_fee * p.price) as avg_tx_fee_usd

FROM ethereum.core.fact_transactions t

LEFT JOIN ethereum.price.ez_prices_hourly p

ON DATE_TRUNC('hour', t.block_timestamp) = p.hour

AND p.symbol = 'ETH'

WHERE t.block_timestamp >= CURRENT_DATE - INTERVAL '{{days}} days'

AND t.status = 'SUCCESS'

GROUP BY 1, 2

UNION ALL

-- Near daily fees with price

SELECT

'near' as blockchain,

DATE_TRUNC('day', t.block_timestamp) as date,

AVG((t.transaction_fee / POW(10, 24)) * p.price) as avg_tx_fee_usd