WITH polygon as (
select
block_timestamp::date as date,
block_id,
count(distinct tx_id) as txs
FROM flipside_prod_db.polygon.transactions
WHERE block_timestamp >= '2022-06-15'
GROUP BY 1,2
), optimism as (
select
block_timestamp::date as date,
block_number,
count(distinct tx_hash) as txs
FROM optimism.core.fact_transactions
WHERE block_timestamp >= '2022-06-15'
GROUP BY 1,2
), arbitrum as (
SELECT
block_timestamp::date as date,
block_number,
count(distinct tx_hash) as txs
from arbitrum.core.fact_transactions
WHERE block_timestamp >= '2022-06-15'
GROUP by 1,2
)
SELECT
date,
'polygon' as chain,
AVG(txs) as average_txn_per_block,
MAX(txs) as max_txn_per_block,
MIN(txs) as min_txn_per_block
from polygon
group by 1
UNION
SELECT
date,