WITH net_flow AS ( 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 ( SELECT DATE(block_timestamp) AS date, COUNT(CASE WHEN amount_in_usd + amount_out_usd <= 100 THEN 1 END) AS small_transactions, COUNT(CASE WHEN amount_in_usd + amount_out_usd > 100 AND amount_in + amount_out <= 1000 THEN 1 END) AS medium_transactions, COUNT(CASE WHEN amount_in_usd + amount_out_usd > 1000 THEN 1 END) AS large_transactions FROM aptos.defi.ez_dex_swaps WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' GROUP BY date ), token_pair_performance AS ( SELECT DATE(block_timestamp) AS date, token_in AS paired_token, SUM(amount_in_usd + amount_out_usd) AS paired_volume FROM aptos.defi.ez_dex_swaps WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' GROUP BY date, token_in ), whale_activity AS ( SELECT DATE(block_timestamp) AS date, COUNT(*) AS whale_transactions FROM aptos.defi.ez_dex_swaps WHERE (token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' AND amount_in_usd > 1000) OR (token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' AND amount_out_usd > 1000) GROUP BY date ), cumulative_metrics AS ( WITH daily_totals AS ( SELECT DATE(block_timestamp) AS date, SUM(amount_in_usd + amount_out_usd) AS daily_volume, COUNT(DISTINCT swapper) AS daily_unique_swappers FROM aptos.defi.ez_dex_swaps WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' GROUP BY date ) SELECT date, SUM(daily_volume) OVER (ORDER BY date) AS cumulative_volume, SUM(daily_unique_swappers) OVER (ORDER BY date) AS cumulative_unique_swappers FROM daily_totals ), historical_comparison AS ( WITH weekly_totals AS ( SELECT DATE_TRUNC('week', block_timestamp) AS week, SUM(amount_in_usd + amount_out_usd) AS weekly_volume, COUNT(DISTINCT swapper) AS weekly_unique_swappers FROM aptos.defi.ez_dex_swaps WHERE token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG' GROUP BY DATE_TRUNC('week', block_timestamp) ) SELECT week AS date, weekly_volume, weekly_unique_swappers, LAG(weekly_volume) OVER (ORDER BY week) AS previous_week_volume, LAG(weekly_unique_swappers) OVER (ORDER BY week) AS previous_week_swappers, (weekly_volume - LAG(weekly_volume) OVER (ORDER BY week)) / NULLIF(LAG(weekly_volume) OVER (ORDER BY week), 0) * 100 AS volume_change_pct, (weekly_unique_swappers - LAG(weekly_unique_swappers) OVER (ORDER BY week)) / NULLIF(LAG(weekly_unique_swappers) OVER (ORDER BY week), 0) * 100 AS user_change_pct FROM weekly_totals ) SELECT nf.date, nf.buy_volume, nf.sell_volume, ur.total_unique_swappers, ur.new_users, ur.returning_users, ts.small_transactions, ts.medium_transactions, ts.large_transactions, tp.paired_token, tp.paired_volume, wa.whale_transactions, cm.cumulative_volume, cm.cumulative_unique_swappers, hc.weekly_volume, hc.weekly_unique_swappers, hc.previous_week_volume, hc.previous_week_swappers, hc.volume_change_pct, hc.user_change_pct FROM net_flow nf LEFT JOIN user_retention ur ON nf.date = ur.date LEFT JOIN transaction_size ts ON nf.date = ts.date LEFT JOIN token_pair_performance tp ON nf.date = tp.date LEFT JOIN whale_activity wa ON nf.date = wa.date LEFT JOIN cumulative_metrics cm ON nf.date = cm.date LEFT JOIN historical_comparison hc ON nf.date = hc.date WHERE nf.buy_volume IS NOT NULL AND nf.buy_volume <> 0 OR nf.sell_volume IS NOT NULL AND nf.sell_volume <> 0 OR ur.total_unique_swappers IS NOT NULL AND ur.total_unique_swappers <> 0 OR ur.new_users IS NOT NULL AND ur.new_users <> 0 OR ur.returning_users IS NOT NULL AND ur.returning_users <> 0 OR ts.small_transactions IS NOT NULL AND ts.small_transactions <> 0 OR ts.medium_transactions IS NOT NULL AND ts.medium_transactions <> 0 OR ts.large_transactions IS NOT NULL AND ts.large_transactions <> 0 OR tp.paired_volume IS NOT NULL AND tp.paired_volume <> 0 OR wa.whale_transactions IS NOT NULL AND wa.whale_transactions <> 0 OR cm.cumulative_volume IS NOT NULL AND cm.cumulative_volume <> 0 OR cm.cumulative_unique_swappers IS NOT NULL AND cm.cumulative_unique_swappers <> 0 OR hc.weekly_volume IS NOT NULL AND hc.weekly_volume <> 0 OR hc.weekly_unique_swappers IS NOT NULL AND hc.weekly_unique_swappers <> 0 OR hc.previous_week_volume IS NOT NULL AND hc.previous_week_volume <> 0 OR hc.previous_week_swappers IS NOT NULL AND hc.previous_week_swappers <> 0 OR hc.volume_change_pct IS NOT NULL AND hc.volume_change_pct <> 0 OR hc.user_change_pct IS NOT NULL AND hc.user_change_pct <> 0 ORDER BY nf.date;

Aptos $EDOG Token Analytics: Market Activity & Holder Insights