SELECT

date_trunc('day', block_timestamp) as day,

'arbitrum' as block_chain,

count(DISTINCT from_address) as users,

count(DISTINCT tx_hash) as events,

sum(TX_FEE) as sum_tx

FROM arbitrum.core.fact_transactions

WHERE block_timestamp > CURRENT_DATE - 60

GROUP BY 1,2

), tab2 as (

SELECT

date_trunc('day', block_timestamp) as day,

'optimism' as block_chain,

count(DISTINCT from_address) as users,

count(DISTINCT tx_hash) as events,

sum(TX_FEE) as sum_tx

FROM optimism.core.fact_transactions

WHERE block_timestamp > CURRENT_DATE - 60

GROUP BY 1,2

), tab3 as (

SELECT

day1 as day,

'polygon' as block_chain,

count(DISTINCT from_address) as users,

count(DISTINCT tx_hash) as events,

(sum(TX_FEE) * avg(matic_price))/avg(eth_price) as sum_tx

FROM polygon.core.fact_transactions

LEFT outer JOIN (

SELECT

date_trunc('day', hour) as day1,

avg(CASE when symbol LIKE 'WETH' then price END) as eth_price,

avg(CASE when symbol LIKE 'MATIC' then price END) as matic_price

FROM ethereum.core.fact_hourly_token_prices

GROUP BY 1

) ON date_trunc('day', block_timestamp) = day1