SELECT

DATE_TRUNC('month', block_timestamp) AS month,

origin_from_address AS trader,

symbol_in,

symbol_out,

COALESCE(amount_in_usd, amount_out_usd) AS amount_usd

FROM

optimism.defi.ez_dex_swaps

WHERE

platform ilike '%uniswap%'

AND amount_usd is not NULL

AND block_timestamp::date >= '2024-01-01'

),

traded_tokens AS (

SELECT

month,

CONCAT(symbol_in, '-', symbol_out) AS token_pair,

trader,

amount_usd

FROM

uniswap_swaps

),

token_pair_popularity AS (

SELECT

month,

token_pair,

COUNT(DISTINCT trader) AS traders,

SUM(amount_usd) AS total_volume_usd,

ROW_NUMBER() OVER (PARTITION BY month ORDER BY SUM(amount_usd) DESC) AS rank

FROM

traded_tokens

GROUP BY

month, token_pair