WITH poly_tx_vol AS (
SELECT
date(block_timestamp) AS date,
COUNT(*) as tx_vol
FROM polygon.core.fact_transactions
WHERE status = 'SUCCESS'
GROUP BY 1
),
eth_tx_vol AS (
SELECT
date(block_timestamp) AS date,
COUNT(*) as tx_vol
FROM ethereum.core.fact_transactions
WHERE status = 'SUCCESS'
GROUP BY 1
)
SELECT
p.date AS "Date",
p.tx_vol AS "Polygon transaction volume",
e.tx_vol AS "Ethereum transaction volume"
FROM poly_tx_vol p
JOIN eth_tx_vol e on p.date = e.date
WHERE p.date BETWEEN '2022-07-01' AND '2022-07-08'
ORDER BY 1;