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-04-20'
GROUP BY 1,2
), solana as (
select
block_timestamp::date as date,
block_id,
count(distinct tx_id) as txs
FROM solana.core.fact_transactions
WHERE block_timestamp >= '2022-04-20'
GROUP BY 1,2
), flow as (
SELECT
block_timestamp::date as date,
block_height,
count(distinct tx_id) as txs
from flow.core.fact_transactions
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,
'solana' as chain,