with klay_price AS (

SELECT hour

,price

FROM kaia.price.ez_prices_hourly

WHERE symbol = 'KLAY'

AND hour::date > current_date - interval '2 week'

),

eth_price AS (

SELECT hour

,price

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

AND hour::date > current_date - interval '2 week'

),

apt_price AS (

SELECT hour

,price

FROM aptos.price.ez_prices_hourly

WHERE token_address = '0x1::aptos_coin::AptosCoin'

AND hour::date > current_date - interval '2 week'

SELECT date_trunc('day', block_timestamp) AS date

,'Aurora' AS blockchain

,sum(tx_fee * price) AS tx_fee

,avg(tx_fee * price) AS avg_tx_fee

FROM aurora.core.fact_transactions a

JOIN eth_price b on date_trunc('hour', block_timestamp) = b.hour

WHERE block_timestamp::date > current_date - interval '2 week'

AND status = 'SUCCESS'

GROUP BY 1