1-- Complete Transaction Count by Type (All Types)
2SELECT 
3    'Payment' as transaction_type,
4    SUM(COALESCE(type_payment, 0)) as total_transactions,
5    ROUND(AVG(COALESCE(type_payment, 0)), 0) as avg_daily_transactions,
6    MAX(COALESCE(type_payment, 0)) as max_daily_transactions
7FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
8
9UNION ALL SELECT 'OfferCreate', SUM(COALESCE(type_offercreate, 0)), ROUND(AVG(COALESCE(type_offercreate, 0)), 0), MAX(COALESCE(type_offercreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
10UNION ALL SELECT 'TrustSet', SUM(COALESCE(type_trustset, 0)), ROUND(AVG(COALESCE(type_trustset, 0)), 0), MAX(COALESCE(type_trustset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
11UNION ALL SELECT 'OfferCancel', SUM(COALESCE(type_offercancel, 0)), ROUND(AVG(COALESCE(type_offercancel, 0)), 0), MAX(COALESCE(type_offercancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
12UNION ALL SELECT 'NFTokenMint', SUM(COALESCE(type_nftokenmint, 0)), ROUND(AVG(COALESCE(type_nftokenmint, 0)), 0), MAX(COALESCE(type_nftokenmint, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
13UNION ALL SELECT 'NFTokenAcceptOffer', SUM(COALESCE(type_nftokenacceptoffer, 0)), ROUND(AVG(COALESCE(type_nftokenacceptoffer, 0)), 0), MAX(COALESCE(type_nftokenacceptoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
14UNION ALL SELECT 'NFTokenCreateOffer', SUM(COALESCE(type_nftokencreateoffer, 0)), ROUND(AVG(COALESCE(type_nftokencreateoffer, 0)), 0), MAX(COALESCE(type_nftokencreateoffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
15UNION ALL SELECT 'NFTokenCancelOffer', SUM(COALESCE(type_nftokencanceloffer, 0)), ROUND(AVG(COALESCE(type_nftokencanceloffer, 0)), 0), MAX(COALESCE(type_nftokencanceloffer, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
16UNION ALL SELECT 'NFTokenBurn', SUM(COALESCE(type_nftokenburn, 0)), ROUND(AVG(COALESCE(type_nftokenburn, 0)), 0), MAX(COALESCE(type_nftokenburn, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
17UNION ALL SELECT 'NFTokenModify', SUM(COALESCE(type_nftokenmodify, 0)), ROUND(AVG(COALESCE(type_nftokenmodify, 0)), 0), MAX(COALESCE(type_nftokenmodify, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
18UNION ALL SELECT 'AccountSet', SUM(COALESCE(type_accountset, 0)), ROUND(AVG(COALESCE(type_accountset, 0)), 0), MAX(COALESCE(type_accountset, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
19UNION ALL SELECT 'AccountDelete', SUM(COALESCE(type_accountdelete, 0)), ROUND(AVG(COALESCE(type_accountdelete, 0)), 0), MAX(COALESCE(type_accountdelete, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
20UNION ALL SELECT 'AMMDeposit', SUM(COALESCE(type_ammdeposit, 0)), ROUND(AVG(COALESCE(type_ammdeposit, 0)), 0), MAX(COALESCE(type_ammdeposit, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
21UNION ALL SELECT 'AMMWithdraw', SUM(COALESCE(type_ammwithdraw, 0)), ROUND(AVG(COALESCE(type_ammwithdraw, 0)), 0), MAX(COALESCE(type_ammwithdraw, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
22UNION ALL SELECT 'AMMCreate', SUM(COALESCE(type_ammcreate, 0)), ROUND(AVG(COALESCE(type_ammcreate, 0)), 0), MAX(COALESCE(type_ammcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
23UNION ALL SELECT 'AMMDelete', SUM(COALESCE(type_ammdelete, 0)), ROUND(AVG(COALESCE(type_ammdelete, 0)), 0), MAX(COALESCE(type_ammdelete, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
24UNION ALL SELECT 'AMMBid', SUM(COALESCE(type_ammbid, 0)), ROUND(AVG(COALESCE(type_ammbid, 0)), 0), MAX(COALESCE(type_ammbid, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
25UNION ALL SELECT 'AMMVote', SUM(COALESCE(type_ammvote, 0)), ROUND(AVG(COALESCE(type_ammvote, 0)), 0), MAX(COALESCE(type_ammvote, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
26UNION ALL SELECT 'CheckCreate', SUM(COALESCE(type_checkcreate, 0)), ROUND(AVG(COALESCE(type_checkcreate, 0)), 0), MAX(COALESCE(type_checkcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
27UNION ALL SELECT 'CheckCancel', SUM(COALESCE(type_checkcancel, 0)), ROUND(AVG(COALESCE(type_checkcancel, 0)), 0), MAX(COALESCE(type_checkcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
28UNION ALL SELECT 'CheckCash', SUM(COALESCE(type_checkcash, 0)), ROUND(AVG(COALESCE(type_checkcash, 0)), 0), MAX(COALESCE(type_checkcash, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
29UNION ALL SELECT 'EscrowCreate', SUM(COALESCE(type_escrowcreate, 0)), ROUND(AVG(COALESCE(type_escrowcreate, 0)), 0), MAX(COALESCE(type_escrowcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
30UNION ALL SELECT 'EscrowCancel', SUM(COALESCE(type_escrowcancel, 0)), ROUND(AVG(COALESCE(type_escrowcancel, 0)), 0), MAX(COALESCE(type_escrowcancel, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
31UNION ALL SELECT 'EscrowFinish', SUM(COALESCE(type_escrowfinish, 0)), ROUND(AVG(COALESCE(type_escrowfinish, 0)), 0), MAX(COALESCE(type_escrowfinish, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
32UNION ALL SELECT 'PaymentChannelCreate', SUM(COALESCE(type_paymentchannelcreate, 0)), ROUND(AVG(COALESCE(type_paymentchannelcreate, 0)), 0), MAX(COALESCE(type_paymentchannelcreate, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
33UNION ALL SELECT 'PaymentChannelClaim', SUM(COALESCE(type_paymentchannelclaim, 0)), ROUND(AVG(COALESCE(type_paymentchannelclaim, 0)), 0), MAX(COALESCE(type_paymentchannelclaim, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
34UNION ALL SELECT 'PaymentChannelFund', SUM(COALESCE(type_paymentchannelfund, 0)), ROUND(AVG(COALESCE(type_paymentchannelfund, 0)), 0), MAX(COALESCE(type_paymentchannelfund, 0)) FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
35
36ORDER BY total_transactions DESC;