WITH arbitrum_tx AS(
SELECT
date(block_timestamp) AS date,
COUNT(DISTINCT tx_hash) AS tx_vol
FROM arbitrum.core.fact_transactions
WHERE status = 'SUCCESS'
GROUP BY 1
),
bsc_tx AS(
SELECT
date(block_timestamp) AS date,
COUNT(DISTINCT tx_hash) AS tx_vol
FROM bsc.core.fact_transactions
WHERE status = 'SUCCESS'
GROUP BY 1
)
SELECT
a.date AS "Date",
a.tx_vol AS "Arbitrum",
b.tx_vol AS "BSC"
FROM arbitrum_tx a
JOIN bsc_tx b ON a.date = b.date
WHERE a.date >= dateadd(DAY, -30, getdate())
GROUP BY 1, 2, 3
ORDER BY 1 DESC;