SELECT

DATE_TRUNC('day', block_timestamp) as trading_day,

platform,

COUNT(DISTINCT tx_hash) as daily_trades,

AVG(CASE WHEN amount_in_usd IS NOT NULL THEN amount_in_usd ELSE 0 END) as avg_trade_size_usd,

SUM(CASE WHEN amount_in_usd IS NOT NULL THEN amount_in_usd ELSE 0 END) as daily_volume_usd

FROM base.defi.ez_dex_swaps

WHERE block_timestamp >= CURRENT_DATE - 7 -- Last 7 days for recent activity

GROUP BY 1, 2

),

platform_summary AS (

SELECT

platform,

AVG(daily_trades) as avg_daily_trades,

AVG(avg_trade_size_usd) as avg_trade_size_usd,

AVG(daily_volume_usd) as avg_daily_volume_usd,

COUNT(DISTINCT trading_day) as days_active

FROM daily_platform_metrics

-- Filter for platforms with meaningful activity

GROUP BY 1

HAVING days_active >= 3 -- Active at least 3 days

AND avg_daily_trades > 5 -- More than 5 trades per day on average

SELECT

platform,

ROUND(avg_daily_trades, 2) as avg_daily_trades,

ROUND(avg_trade_size_usd, 2) as avg_trade_size_usd,

ROUND(avg_daily_volume_usd, 2) as avg_daily_volume_usd,

days_active

FROM platform_summary

ORDER BY avg_daily_volume_usd DESC;