base as (

SELECT 'Ethereum' as Chain,

ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

FROM ethereum.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'

GROUP by 1,2

SELECT 'Polygon' as Chain,

ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

FROM polygon.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'

GROUP by 1,2

SELECT 'Optimism' as Chain,

ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP

FROM optimism.core.ez_dex_swaps

WHERE PLATFORM LIKE '%uniswap%'

GROUP by 1,2),

final as (

SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM ethereum.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%' UNION

SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM polygon.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%' UNION

SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM optimism.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'

SELECT Chain, count (DISTINCT t1.ORIGIN_FROM_ADDRESS) as New_Users, sum (volume) as Volumes,Median (volume) as AVG_Volumes,Min (volume) as Min_Volumes ,Max (volume) as Max_Volumes

FROM base t1 LEFT JOIN final T2 ON T1.BLOCK_TIMESTAMP = T2.BLOCK_TIMESTAMP AND T1.ORIGIN_FROM_ADDRESS = T2.ORIGIN_FROM_ADDRESS WHERE Chain = 'Ethereum'

AND t1.BLOCK_TIMESTAMP >= current_date -365 AND t1.BLOCK_TIMESTAMP = t2.BLOCK_TIMESTAMP GROUP BY 1 UNION

SELECT Chain, count (DISTINCT t1.ORIGIN_FROM_ADDRESS) as New_Users, sum (volume) as Volumes,Median (volume) as AVG_Volumes,Min (volume) as Min_Volumes ,Max (volume) as Max_Volumes

FROM base t1 LEFT JOIN final T2 ON T1.BLOCK_TIMESTAMP = T2.BLOCK_TIMESTAMP AND T1.ORIGIN_FROM_ADDRESS = T2.ORIGIN_FROM_ADDRESS WHERE Chain = 'Polygon'

AND t1.BLOCK_TIMESTAMP >= current_date -365 AND T1.BLOCK_TIMESTAMP = T2.BLOCK_TIMESTAMP GROUP BY 1 UNION