1-- AMM Volume Analysis: Daily activity, average volume, and cumulative metrics
2SELECT 
3    date::date as date,
4    -- Daily AMM activity volume (deposits + withdrawals)
5    (COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) as daily_amm_transactions,
6    
7    -- XRP locked in AMM pools as a volume proxy
8    COALESCE(amm_xrp_locked, 0) as amm_xrp_locked,
9    
10    -- Rolling 7-day average of AMM transactions
11    AVG(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) OVER (
12        ORDER BY date::date 
13        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
14    ) as avg_amm_transactions_7d,
15    
16    -- Cumulative AMM transactions
17    SUM(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0)) OVER (
18        ORDER BY date::date 
19        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
20    ) as cumulative_amm_transactions,
21    
22    -- Number of active AMM pools
23    COALESCE(amm_amm_count, 0) as active_amm_pools
24    
25FROM @xrpscan.xrp_ledger.metrics
26WHERE date >= '2024-08-01'  -- AMMs were introduced later
27  AND (type_ammdeposit IS NOT NULL OR type_ammwithdraw IS NOT NULL OR amm_xrp_locked IS NOT NULL)
28ORDER BY date::date ASC;