OneDataAnalystSwap ETH-Stablecoins
    Updated 2022-09-25
    WITH t1 AS (
    SELECT Date_trunc('day',BLOCK_TIMESTAMP) AS Date, SUM(AMOUNT_IN) AS Stable_to_eth_amount_sent, SUM(AMOUNT_OUT) AS Stable_to_eth_amount_received,count(*) AS Stable_to_eth_num
    FROM ethereum.core.ez_dex_swaps
    WHERE SYMBOL_IN IN ('DAI','BUSD','USDC','USDT','USDT')
    AND SYMBOL_OUT IN ('WETH','stETH','rETH','aETH')
    AND date >= CURRENT_DATE - 60
    AND Date < Current_date
    GROUP BY 1
    ),

    t2 AS (
    SELECT Date_trunc('day',BLOCK_TIMESTAMP) AS Dt, SUM(AMOUNT_IN) AS eth_to_Stable_amount_sent, SUM(AMOUNT_OUT) AS eth_to_Stable_amount_received,count(*) AS eth_to_Stable_num
    FROM ethereum.core.ez_dex_swaps
    WHERE SYMBOL_OUT IN ('DAI','BUSD','USDC','USDT','USDT')
    AND SYMBOL_IN IN ('WETH','stETH','rETH','aETH')
    AND dt >= CURRENT_DATE - 60
    AND dt < Current_date
    GROUP BY 1
    )

    SELECT Date,
    Stable_to_eth_amount_sent,
    Stable_to_eth_amount_received,
    eth_to_Stable_amount_sent,
    eth_to_Stable_amount_received,
    Stable_to_eth_num,
    eth_to_Stable_num,
    Stable_to_eth_amount_sent-eth_to_Stable_amount_received AS Balance_swaps_usd,
    Stable_to_eth_amount_received-eth_to_Stable_amount_sent AS Balance_swaps_eth,
    IFF(date = '2022-09-15', 'Merge-Day', IFF(date < '2022-09-15','Pre-Merge','Post-Merge')) AS Merge_status,

    AVG(Stable_to_eth_amount_sent) OVER (Partition BY Merge_status) AS AVG_Stable_to_eth_sent,
    AVG(Stable_to_eth_amount_received) OVER (Partition BY Merge_status) AS AVG_Stable_to_eth_received,
    AVG(eth_to_Stable_amount_sent) OVER (Partition BY Merge_status) AS AVG_eth_to_Stable_sent,
    AVG(eth_to_Stable_amount_received) OVER (Partition BY Merge_status) AS AVG_eth_to_Stable_received,
    Run a query to Download Data