select date_trunc('day',BLOCK_TIMESTAMP) as date,

'Arbitrum' as chain,

count(tx_hash) as count_txs,

count_txs/86400 as avg_tps,

count(DISTINCT FROM_ADDRESS) as count_wallets,

min(GAS_PRICE_PAID) as min_gas_price,

median(GAS_PRICE_PAID) as median_gas_price,

avg(GAS_PRICE_PAID) as avg_gas_price,

max(GAS_PRICE_PAID) as max_gas_price, --gwei

avg(GAS_USED) as avg_gas_used,

sum(GAS_USED) as sum_gas_used,

sum(TX_FEE) as sum_fee, --eth

sum(count_txs) over (order by date) as cum_txs,

sum(count_wallets) over (order by date) as cum_wallets,

sum(sum_gas_used) over (order by date) as cum_gas_used,

sum(sum_fee) over (order by date) as cum_fee

from arbitrum.core.fact_transactions where STATUS = 'SUCCESS' and date >= '2022-06-17' and date != CURRENT_DATE

group by 1

UNION ALL

select date_trunc('day',BLOCK_TIMESTAMP) as date,

'Optimism' as chain,

count(tx_hash) as count_txs,

count_txs/86400 as avg_tps,

count(DISTINCT FROM_ADDRESS) as count_wallets,

min(L1_GAS_PRICE+GAS_PRICE) as min_gas_price,

median(L1_GAS_PRICE+GAS_PRICE) as median_gas_price,

avg(L1_GAS_PRICE+GAS_PRICE) as avg_gas_price,

max(L1_GAS_PRICE+GAS_PRICE) as max_gas_price, --gwei

avg(GAS_USED+L1_GAS_USED) as avg_gas_used,

sum(GAS_USED+L1_GAS_USED) as sum_gas_used,

sum(TX_FEE) as sum_fee, --eth

sum(count_txs) over (order by date) as cum_txs,

sum(count_wallets) over (order by date) as cum_wallets,