(SELECT

BLOCKCHAIN AS blockchain,

PLATFORM AS liquidity_venue,

CREATION_TIME AS pool_creation,

POOL_NAME AS pool_name,

POOL_ADDRESS AS pool_address,

SYMBOLS AS tokens,

CASE WHEN tokens:token0 = '0x9f52c8ecbee10e00d9faaac5ee9ba0ff6550f511' THEN 'SIPHER'

WHEN tokens:token0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN 'WETH'

WHEN tokens:token0 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 'USDC'

ELSE tokens:token0 END AS token0,

CASE WHEN tokens:token1 = '0x9f52c8ecbee10e00d9faaac5ee9ba0ff6550f511' THEN 'SIPHER'

WHEN tokens:token1 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN 'WETH'

WHEN tokens:token1 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 'USDC'

ELSE tokens:token1 END AS token1

FROM crosschain.defi.dim_dex_liquidity_pools

WHERE (tokens:token0 IN ('0x9f52c8ecbee10e00d9faaac5ee9ba0ff6550f511',

'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',

'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')

AND tokens:token1 IN ('0x9f52c8ecbee10e00d9faaac5ee9ba0ff6550f511',

'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',

'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'))

AND pool_name LIKE '%SIPHER%'),

q2_volume_data AS

CONTRACT_ADDRESS AS pool_address,

SUM(AMOUNT_OUT_USD) AS usd_volume,

SUM(AMOUNT_OUT) AS token_amount,

SUM(AMOUNT_OUT_USD)/SUM(AMOUNT_OUT) AS price

FROM ethereum.defi.ez_dex_swaps

WHERE MODIFIED_TIMESTAMP BETWEEN '2024-07-01' AND '2024-09-30'

AND TOKEN_OUT = '0x9f52c8ecbee10e00d9faaac5ee9ba0ff6550f511'

GROUP BY CONTRACT_ADDRESS),