ThirtyDayData AS (

-- Calculate metrics for the last 30 days for all blockchains

SELECT

blockchain,

COUNT(*) as sales_30d,

COUNT(DISTINCT BUYER_ADDRESS) as buyers_30d,

SUM(PRICE_USD) as sales_volume_usd_30d

FROM (

SELECT 'Ethereum' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM ethereum.core.ez_nft_sales

UNION ALL

SELECT 'Polygon' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM polygon.core.ez_nft_sales

UNION ALL

SELECT 'Avalanche' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM avalanche.core.ez_nft_sales

UNION ALL

SELECT 'Arbitrum' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM arbitrum.core.ez_nft_sales

UNION ALL

SELECT 'Optimism' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM optimism.core.ez_nft_sales

) AS combined

WHERE BLOCK_TIMESTAMP > current_date - 30

GROUP BY blockchain

),

SixtyDayData AS (

-- Calculate metrics for the 30 days before the last 30 days for all blockchains

SELECT

blockchain,

COUNT(*) as sales_60d,

COUNT(DISTINCT BUYER_ADDRESS) as buyers_60d,

SUM(PRICE_USD) as sales_volume_usd_60d

FROM (

SELECT 'Ethereum' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM ethereum.core.ez_nft_sales

UNION ALL

SELECT 'Polygon' as blockchain, BLOCK_TIMESTAMP, BUYER_ADDRESS, PRICE_USD FROM polygon.core.ez_nft_sales

UNION ALL