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