SELECT

DATE(block_timestamp) AS date,

SUM(CASE WHEN token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

THEN amount_out_usd ELSE 0 END) AS buy_volume,

SUM(CASE WHEN token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

THEN amount_in_usd ELSE 0 END) AS sell_volume

FROM aptos.defi.ez_dex_swaps

WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

GROUP BY date

),

user_retention AS (

WITH first_activity AS (

SELECT

swapper,

MIN(DATE(block_timestamp)) AS first_swap_date

FROM aptos.defi.ez_dex_swaps

WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

GROUP BY swapper

)

SELECT

DATE(s.block_timestamp) AS date,

COUNT(DISTINCT s.swapper) AS total_unique_swappers,

COUNT(DISTINCT CASE WHEN s.swapper = fa.swapper AND DATE(s.block_timestamp) = fa.first_swap_date THEN s.swapper END) AS new_users,

COUNT(DISTINCT CASE WHEN s.swapper = fa.swapper AND DATE(s.block_timestamp) > fa.first_swap_date THEN s.swapper END) AS returning_users

FROM aptos.defi.ez_dex_swaps AS s

JOIN first_activity AS fa ON s.swapper = fa.swapper

WHERE s.token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

OR s.token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'

GROUP BY date

),

transaction_size AS (