WITH usdz_trades_eth AS (

-- Ethereum: Filter for USDz-related swaps on Uniswap

SELECT

DATE_TRUNC('week', block_timestamp) AS week,

'ethereum' AS chain,

platform AS dex,

contract_address as pool_address,

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

SUM(COALESCE(amount_in_usd, amount_out_usd)) AS weekly_volume_usd

FROM

ethereum.defi.ez_dex_swaps

WHERE (symbol_in = 'USDz' OR symbol_out = 'USDz')

AND amount_in_usd IS NOT NULL

AND amount_out_usd IS NOT NULL

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

GROUP BY

week, chain, dex, pool_address,pool_pair

),

usdz_trades_base AS (

-- Base: Filter for USDz-related swaps on Uniswap, Aerodrome, Base Swap

SELECT

DATE_TRUNC('week', block_timestamp) AS week,

'base' AS chain,

platform AS dex,

contract_address as pool_address,

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

SUM(COALESCE(amount_in_usd, amount_out_usd)) AS weekly_volume_usd

FROM

base.defi.ez_dex_swaps

WHERE

(symbol_in = 'USDz' OR symbol_out = 'USDz')

AND amount_in_usd IS NOT NULL

AND amount_out_usd IS NOT NULL

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