M_amani72Ranking Celsius wallets base on Value of swaps $ (sold/bought)
    Updated 2022-06-22
    WITH celsius_wallet AS (
    SELECT ADDRESS
    FROM flipside_prod_db.crosschain.address_labels
    WHERE ADDRESS_NAME='celsius wallet'
    ),
    finsl AS (
    SELECT TX_TO AS celsius_wallet,COUNT(TX_HASH) Number_of_swap,sum(AMOUNT_OUT_USD) Value_base_on_USD ,'bought' type
    FROM ethereum.core.ez_dex_swaps
    WHERE PLATFORM IN ('sushiswap','uniswap-v2','uniswap-v3')
    AND TX_TO IN (SELECT * FROM celsius_wallet)
    GROUP BY 1
    UNION
    SELECT ORIGIN_FROM_ADDRESS,COUNT(TX_HASH) Number_of_swap,sum(AMOUNT_IN_USD) Value_base_on_USD,'sold' type
    FROM ethereum.core.ez_dex_swaps
    WHERE PLATFORM IN ('sushiswap','uniswap-v2','uniswap-v3')
    AND ORIGIN_FROM_ADDRESS IN (SELECT * FROM celsius_wallet)
    GROUP BY 1
    )
    SELECT *,row_number() OVER (PARTITION BY type ORDER BY Value_base_on_USD DESC) AS Rank
    FROM finsl
    WHERE VALUE_BASE_ON_USD is NOT NULL
    ORDER BY Rank ASC
    LIMIT 20
    Run a query to Download Data