drone-mostafaMultichain VOLUME DAILY TOTAL
    Updated 2023-05-13
    WITH
    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

    UNION

    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

    UNION

    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
    Run a query to Download Data