SELECT

DATE_TRUNC('day', block_timestamp) as date,

source_chain,

destination_chain,

platform,

COUNT(DISTINCT source_address) as daily_unique_users,

COUNT(*) as daily_tx_count,

SUM(amount_usd) as daily_volume_usd,

COUNT(DISTINCT tx_hash) as daily_unique_tx

FROM crosschain.defi.ez_bridge_activity

WHERE block_timestamp >= DATEADD(day, -90, CURRENT_DATE)

AND amount_usd IS NOT NULL

GROUP BY 1,2,3,4

),

window_stats AS (

SELECT

source_chain,

destination_chain,

platform,

-- Last 24 hours

SUM(CASE WHEN date >= DATEADD(day, -1, CURRENT_DATE) THEN daily_unique_users END) as users_1d,

SUM(CASE WHEN date >= DATEADD(day, -1, CURRENT_DATE) THEN daily_volume_usd END) as volume_1d,

-- Last 7 days

SUM(CASE WHEN date >= DATEADD(day, -7, CURRENT_DATE) THEN daily_unique_users END) as users_7d,

SUM(CASE WHEN date >= DATEADD(day, -7, CURRENT_DATE) THEN daily_volume_usd END) as volume_7d,

-- Last 30 days

SUM(CASE WHEN date >= DATEADD(day, -30, CURRENT_DATE) THEN daily_unique_users END) as users_30d,

SUM(CASE WHEN date >= DATEADD(day, -30, CURRENT_DATE) THEN daily_volume_usd END) as volume_30d,

-- Last 90 days

SUM(CASE WHEN date >= DATEADD(day, -90, CURRENT_DATE) THEN daily_unique_users END) as users_90d,

SUM(CASE WHEN date >= DATEADD(day, -90, CURRENT_DATE) THEN daily_volume_usd END) as volume_90d,

-- Calculate average daily metrics for momentum

AVG(CASE WHEN date >= DATEADD(day, -7, CURRENT_DATE) THEN daily_volume_usd ELSE NULL END) as avg_daily_volume_7d,