-- Top 10 Most Frequent Transaction Types (Last 7 Days)
WITH recent_transactions AS (
    SELECT 
        'Payment' as transaction_type,
        SUM(COALESCE(type_payment, 0)) as total_transactions
    FROM @xrpscan.xrp_ledger.metrics
    WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    
    UNION ALL
    SELECT 'OfferCreate', SUM(COALESCE(type_offercreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'TrustSet', SUM(COALESCE(type_trustset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'OfferCancel', SUM(COALESCE(type_offercancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'NFTokenMint', SUM(COALESCE(type_nftokenmint, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'NFTokenAcceptOffer', SUM(COALESCE(type_nftokenacceptoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'NFTokenCreateOffer', SUM(COALESCE(type_nftokencreateoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'NFTokenCancelOffer', SUM(COALESCE(type_nftokencanceloffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'NFTokenBurn', SUM(COALESCE(type_nftokenburn, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AccountSet', SUM(COALESCE(type_accountset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AccountDelete', SUM(COALESCE(type_accountdelete, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AMMDeposit', SUM(COALESCE(type_ammdeposit, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AMMWithdraw', SUM(COALESCE(type_ammwithdraw, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AMMCreate', SUM(COALESCE(type_ammcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AMMVote', SUM(COALESCE(type_ammvote, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'AMMBid', SUM(COALESCE(type_ammbid, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'CheckCreate', SUM(COALESCE(type_checkcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'CheckCancel', SUM(COALESCE(type_checkcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'CheckCash', SUM(COALESCE(type_checkcash, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'EscrowCreate', SUM(COALESCE(type_escrowcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'EscrowCancel', SUM(COALESCE(type_escrowcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'EscrowFinish', SUM(COALESCE(type_escrowfinish, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'PaymentChannelCreate', SUM(COALESCE(type_paymentchannelcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'PaymentChannelClaim', SUM(COALESCE(type_paymentchannelclaim, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
    UNION ALL
    SELECT 'PaymentChannelFund', SUM(COALESCE(type_paymentchannelfund, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
)

SELECT 
    ROW_NUMBER() OVER (ORDER BY total_transactions DESC) as rank,
    transaction_type,
    total_transactions,
    ROUND(total_transactions / 7.0, 0) as avg_daily_transactions,
    ROUND((total_transactions::float / SUM(total_transactions) OVER ()) * 100, 2) as percentage_of_total
FROM recent_transactions
WHERE total_transactions > 0
ORDER BY total_transactions DESC
LIMIT 10;