1-- Top 10 Most Frequent Transaction Types (Last 7 Days)
2WITH recent_transactions AS (
3    SELECT 
4        'Payment' as transaction_type,
5        SUM(COALESCE(type_payment, 0)) as total_transactions
6    FROM @xrpscan.xrp_ledger.metrics
7    WHERE date >= CURRENT_DATE - INTERVAL '7 days'
8    
9    UNION ALL
10    SELECT 'OfferCreate', SUM(COALESCE(type_offercreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
11    UNION ALL
12    SELECT 'TrustSet', SUM(COALESCE(type_trustset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
13    UNION ALL
14    SELECT 'OfferCancel', SUM(COALESCE(type_offercancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
15    UNION ALL
16    SELECT 'NFTokenMint', SUM(COALESCE(type_nftokenmint, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
17    UNION ALL
18    SELECT 'NFTokenAcceptOffer', SUM(COALESCE(type_nftokenacceptoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
19    UNION ALL
20    SELECT 'NFTokenCreateOffer', SUM(COALESCE(type_nftokencreateoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
21    UNION ALL
22    SELECT 'NFTokenCancelOffer', SUM(COALESCE(type_nftokencanceloffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
23    UNION ALL
24    SELECT 'NFTokenBurn', SUM(COALESCE(type_nftokenburn, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
25    UNION ALL
26    SELECT 'AccountSet', SUM(COALESCE(type_accountset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
27    UNION ALL
28    SELECT 'AccountDelete', SUM(COALESCE(type_accountdelete, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
29    UNION ALL
30    SELECT 'AMMDeposit', SUM(COALESCE(type_ammdeposit, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
31    UNION ALL
32    SELECT 'AMMWithdraw', SUM(COALESCE(type_ammwithdraw, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
33    UNION ALL
34    SELECT 'AMMCreate', SUM(COALESCE(type_ammcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
35    UNION ALL
36    SELECT 'AMMVote', SUM(COALESCE(type_ammvote, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
37    UNION ALL
38    SELECT 'AMMBid', SUM(COALESCE(type_ammbid, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
39    UNION ALL
40    SELECT 'CheckCreate', SUM(COALESCE(type_checkcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
41    UNION ALL
42    SELECT 'CheckCancel', SUM(COALESCE(type_checkcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
43    UNION ALL
44    SELECT 'CheckCash', SUM(COALESCE(type_checkcash, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
45    UNION ALL
46    SELECT 'EscrowCreate', SUM(COALESCE(type_escrowcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
47    UNION ALL
48    SELECT 'EscrowCancel', SUM(COALESCE(type_escrowcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
49    UNION ALL
50    SELECT 'EscrowFinish', SUM(COALESCE(type_escrowfinish, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
51    UNION ALL
52    SELECT 'PaymentChannelCreate', SUM(COALESCE(type_paymentchannelcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
53    UNION ALL
54    SELECT 'PaymentChannelClaim', SUM(COALESCE(type_paymentchannelclaim, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
55    UNION ALL
56    SELECT 'PaymentChannelFund', SUM(COALESCE(type_paymentchannelfund, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= CURRENT_DATE - INTERVAL '7 days'
57)
58
59SELECT 
60    ROW_NUMBER() OVER (ORDER BY total_transactions DESC) as rank,
61    transaction_type,
62    total_transactions,
63    ROUND(total_transactions / 7.0, 0) as avg_daily_transactions,
64    ROUND((total_transactions::float / SUM(total_transactions) OVER ()) * 100, 2) as percentage_of_total
65FROM recent_transactions
66WHERE total_transactions > 0
67ORDER BY total_transactions DESC
68LIMIT 10;