-- AMM Users vs DEX Traders Comparison (Transaction-based Proxy) - Last 500 Days
SELECT 
    date,
    dex_transactions_daily,
    amm_transactions_daily,
    cumulative_dex_transactions,
    cumulative_amm_transactions,
    dex_activity_7d_avg,
    amm_activity_7d_avg,
    active_amm_pools,
    amm_xrp_locked_millions
FROM (
    SELECT 
        date::date as date,
        
        -- DEX Activity (Proxy for DEX Traders)
        (COALESCE(type_offercreate, 0) + COALESCE(type_offercancel, 0)) as dex_transactions_daily,
        
        -- AMM Activity (Proxy for AMM Users) 
        (COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0) + 
         COALESCE(type_ammcreate, 0) + COALESCE(type_ammbid, 0) + 
         COALESCE(type_ammvote, 0)) as amm_transactions_daily,
        
        -- Cumulative activity (total users proxy over time)
        SUM(COALESCE(type_offercreate, 0) + COALESCE(type_offercancel, 0)) OVER (
            ORDER BY date::date
        ) as cumulative_dex_transactions,
        
        SUM(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0) + 
            COALESCE(type_ammcreate, 0) + COALESCE(type_ammbid, 0) + 
            COALESCE(type_ammvote, 0)) OVER (
            ORDER BY date::date
        ) as cumulative_amm_transactions,
        
        -- 7-day rolling averages for smoothed comparison
        AVG(COALESCE(type_offercreate, 0) + COALESCE(type_offercancel, 0)) OVER (
            ORDER BY date::date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as dex_activity_7d_avg,
        
        AVG(COALESCE(type_ammdeposit, 0) + COALESCE(type_ammwithdraw, 0) + 
            COALESCE(type_ammcreate, 0) + COALESCE(type_ammbid, 0) + 
            COALESCE(type_ammvote, 0)) OVER (
            ORDER BY date::date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as amm_activity_7d_avg,
        
        -- Additional context metrics
        amm_amm_count as active_amm_pools,
        ROUND(amm_xrp_locked / 1000000, 2) as amm_xrp_locked_millions
    FROM @xrpscan.xrp_ledger.metrics
    WHERE date >= '2024-03-01'  -- Start from AMM launch
    ORDER BY date::date DESC
    LIMIT 500
) latest_500
ORDER BY date ASC;