SELECT
'Payment' as transaction_type,
SUM(COALESCE(type_payment, 0)) as total_transactions,
ROUND(AVG(COALESCE(type_payment, 0)), 0) as avg_daily_transactions,
MAX(COALESCE(type_payment, 0)) as max_daily_transactions
FROM @xrpscan.xrp_ledger.metrics WHERE date >= '2013-01-01'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
UNION 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'
ORDER BY total_transactions DESC;