SELECT

CONCAT(swap_from_symbol, ' / ', swap_to_symbol) AS "Pair",

COUNT(DISTINCT tx_id) AS "SwapCount",

SUM(swap_from_amount_usd) AS "Volume",

'Solana' AS "Platform",

ROW_NUMBER() OVER (

ORDER BY

COUNT(DISTINCT tx_id) DESC,

SUM(swap_from_amount_usd) DESC

) AS rn

FROM

solana.defi.ez_dex_swaps

WHERE

block_timestamp BETWEEN '2024-10-01'

AND '2024-10-31'

AND swap_from_symbol IS NOT NULL

AND swap_to_symbol IS NOT NULL

AND swap_from_symbol <> ''

AND swap_to_symbol <> ''

GROUP BY

"Pair"

),

EthereumPairs AS (

SELECT

CONCAT(symbol_in, ' / ', symbol_out) AS "Pair",

COUNT(DISTINCT tx_hash) AS "SwapCount",

SUM(amount_in_usd) AS "Volume",

'Ethereum' AS "Platform",

ROW_NUMBER() OVER (

ORDER BY

COUNT(DISTINCT tx_hash) DESC,

SUM(amount_in_usd) DESC

) AS rn

FROM

ethereum.defi.ez_dex_swaps