select

BLOCK_TIMESTAMP::date as date,

concat(symbol_in, '→', symbol_out) as pairs,

count(distinct TX_HASH) as "# Buy TXs",

sum(AMOUNT_IN_USD) as "‌Buy Volume",

count(distinct ORIGIN_FROM_ADDRESS) as "# Buyer",

row_number() over(partition by date order by "‌Buy Volume" desc) as rank

from ethereum.core.ez_dex_swaps

where BLOCK_TIMESTAMP between '2023-01-01' and '2023-01-16'

and EVENT_NAME = 'Swap'

and SYMBOL_OUT in ('WETH', 'ETH')

and symbol_in is not null and symbol_out is not NULL

and AMOUNT_IN_USD > 0

group by 1,2

),

swap_from_ETH as (

select

BLOCK_TIMESTAMP::date as date,

concat(symbol_in, '→', symbol_out) as pairs,

count(distinct TX_HASH) as "# Sell TXs",

sum(AMOUNT_IN_USD) as "Sell Volume",

count(distinct ORIGIN_FROM_ADDRESS) as "# Seller",

row_number() over(partition by date order by "Sell Volume" desc) as rank

from ethereum.core.ez_dex_swaps

where BLOCK_TIMESTAMP between '2023-01-01' and '2023-01-16'

and EVENT_NAME = 'Swap'

and SYMBOL_IN in ('WETH', 'ETH')

and symbol_in is not null and symbol_out is not NULL

and AMOUNT_IN_USD > 0

group by 1,2

)

select * from swap_to_ETH

qualify rank <= 10