-- AMM Volume Analysis: Daily activity, average volume, and cumulative metrics
SELECT 
    date::date as date,
    -- Daily AMM activity volume (deposits + withdrawals)
    (COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) as daily_amm_transactions,
    
    -- XRP locked in AMM pools as a volume proxy
    COALESCE(amm_xrp_locked, 0) as amm_xrp_locked,
    
    -- Rolling 7-day average of AMM transactions
    AVG(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) OVER (
        ORDER BY date::date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as avg_amm_transactions_7d,
    
    -- Cumulative AMM transactions
    SUM(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) OVER (
        ORDER BY date::date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_amm_transactions,
    
    -- Number of active AMM pools
    COALESCE(amm_amm_count, 0) as active_amm_pools
    
FROM @xrpscan.xrp_ledger.metrics
WHERE date >= '2022-01-01'  -- AMMs were introduced later
  AND (type_ammdeposit IS NOT NULL OR type_ammwithdraw IS NOT NULL OR amm_xrp_locked IS NOT NULL)
ORDER BY date::date ASC;