WITH uni_swaps AS (

SELECT date_trunc('day', block_timestamp) as dt, symbol_out, token_out as token_address,

CASE

WHEN amount_out_usd IS NOT NULL THEN amount_out_usd

ELSE amount_in_usd

END AS swap_amount_usd

FROM ethereum_core.ez_dex_swaps

WHERE dt > '2022-01-01' AND

event_name = 'Swap' AND

symbol_out IS NOT NULL AND

platform like 'uniswap%'

),

uni_swaps2 AS (

SELECT dt, symbol_out, sum(swap_amount_usd) as volume, any_value(token_address) as token_address

FROM uni_swaps

GROUP BY 1,2

),

uni_swaps3 AS (

SELECT dt, symbol_out, volume, rank() over (partition by dt order by dt ASC, volume DESC) as daily_rank, token_address,

sum(volume) over (partition by dt, symbol_out order by dt ASC, volume ASC rows between unbounded preceding and current row) as cumsum

FROM uni_swaps2

WHERE volume is not NULL

),

Top20_daily_ranked AS (

SELECT dt, token_address,

CASE

WHEN daily_rank < 10 THEN symbol_out

ELSE 'Other'

END AS symbol_out,

CASE

WHEN daily_rank < 10 THEN volume

ELSE cumsum

END AS volume

FROM uni_swaps3