SELECT

'Ethereum' AS platform_type,

DATE_TRUNC('MONTH', min(block_timestamp)) AS date,

count(distinct tx_hash) as transactions,

count(tx_to) as addresses,

sum(amount_in_usd) as transacted_volume_usd

FROM ethereum.defi.ez_dex_swaps

WHERE block_timestamp >= '2020-05-01'

AND platform IN ('uniswap-v2', 'uniswap-v3')

GROUP BY DATE_TRUNC('MONTH', block_timestamp)

UNION ALL

SELECT

'Layer2s' AS platform_type,

DATE_TRUNC('MONTH', min(block_timestamp)) AS date,

count(distinct tx_hash) as transactions,

count(tx_to) as addresses,

sum(amount_in_usd) as transacted_volume_usd

FROM (

SELECT * FROM optimism.defi.ez_dex_swaps

WHERE block_timestamp >= '2020-05-01'

AND platform IN ('uniswap-v2', 'uniswap-v3')

UNION ALL

SELECT * FROM polygon.defi.ez_dex_swaps

WHERE block_timestamp >= '2020-05-01'

AND platform IN ('uniswap-v2', 'uniswap-v3')

UNION ALL

SELECT * FROM arbitrum.defi.ez_dex_swaps

WHERE block_timestamp >= '2020-05-01'

AND platform IN ('uniswap-v2', 'uniswap-v3')