SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,

COUNT(TX_HASH) AS daily_swaps,

COUNT(DISTINCT SENDER) AS daily_swappers,

SUM(AMOUNT_IN_USD) AS daily_volume

FROM

bsc.defi.ez_dex_swaps

WHERE

BLOCK_TIMESTAMP >= '2024-01-01' AND BLOCK_TIMESTAMP < '2025-01-01'

GROUP BY

DATE_TRUNC('day', BLOCK_TIMESTAMP)

),

annual_stats AS (

SELECT

COUNT(TX_HASH) AS total_swaps,

COUNT(DISTINCT SENDER) AS total_swappers,

SUM(AMOUNT_IN_USD) AS trade_volume

FROM

bsc.defi.ez_dex_swaps

WHERE

BLOCK_TIMESTAMP >= '2024-01-01' AND BLOCK_TIMESTAMP < '2025-01-01'

SELECT

annual_stats.total_swaps,

annual_stats.total_swappers,

annual_stats.trade_volume,

annual_stats.trade_volume / annual_stats.total_swaps AS avg_swap_volume,

annual_stats.trade_volume / annual_stats.total_swappers AS avg_volume_per_swapper,

(SELECT AVG(daily_volume) FROM daily_stats) AS avg_volume_per_day,

(SELECT AVG(daily_swappers) FROM daily_stats) AS avg_swapper_per_day

FROM

annual_stats;