from kaia.core.fact_transactions

),

kaia_price as (

select hour as time, price

from kaia.price.ez_prices_hourly

where symbol = 'KLAY'

),

kaia as (

select kaia_tx.time, sum(price * tx_fee) as tx_fee, 'KAIA' as Network

from kaia_tx left join kaia_price

on kaia_tx.time = kaia_price.time

group by 1

,

eth_tx as (

select date_trunc('hour',block_timestamp) as time, (effective_gas_price - MAX_PRIORITY_FEE_PER_GAS) * gas_used /1e9 as tx_fee

from ethereum.core.fact_transactions

),

eth_price as (

select hour as time, price

from ethereum.price.ez_prices_hourly

where symbol = 'WETH'

,

eth as (

select eth_tx.time, sum(price * tx_fee) as tx_fee, 'Ethereum' as Network

from eth_tx left join eth_price

on eth_tx.time = eth_price.time

group by 1