blockchain AS "Blockchain"

, COUNT(DISTINCT tx_hash) AS "Transactions"

, SUM(amount) AS "Volume"

, COUNT(DISTINCT origin_from_address) AS "Traders"

FROM (

SELECT

'Base' AS blockchain

, tx_hash

, CASE WHEN amount_in_usd IS NOT NULL AND amount_out_usd IS NOT NULL THEN ((amount_in_usd+amount_out_usd)/2)

WHEN amount_in_usd IS NULL AND amount_out_usd IS NULL THEN NULL

WHEN amount_in_usd IS NULL AND amount_out_usd IS NOT NULL THEN amount_out_usd

WHEN amount_in_usd IS NOT NULL AND amount_out_usd IS NULL THEN amount_in_usd

ELSE NULL END AS amount

, origin_from_address

FROM base.defi.ez_dex_swaps

WHERE year(block_timestamp) = '2024'

AND amount IS NOT NULL

GROUP BY 1

UNION ALL

SELECT

blockchain AS "Blockchain"

, COUNT(DISTINCT tx_hash) AS "Transactions"

, SUM(amount) AS "Volume"

, COUNT(DISTINCT origin_from_address) AS "Traders"

FROM (

SELECT

'Avalanche' AS blockchain

, tx_hash

, CASE WHEN amount_in_usd IS NOT NULL AND amount_out_usd IS NOT NULL THEN ((amount_in_usd+amount_out_usd)/2)