1-- Average transaction fees for top 10 transaction types 
2-- (Note: True median requires individual transaction data, not available in aggregated metrics)
3WITH daily_fees_by_type AS (
4    SELECT 
5        date::date,
6        metric_fee_total,
7        metric_transaction_count,
8        
9        -- Calculate estimated fee allocation per transaction type
10        -- Assumes equal fees across transaction types (limitation of aggregated data)
11        CASE 
12            WHEN metric_transaction_count > 0 
13            THEN (metric_fee_total / metric_transaction_count) 
14            ELSE 0 
15        END as avg_fee_per_transaction,
16        
17        type_payment,
18        type_offercreate,
19        type_trustset,
20        type_offercancel,
21        type_nftokenmint,
22        type_nftokenacceptoffer,
23        type_ammdeposit,
24        type_ammwithdraw,
25        type_accountset,
26        type_checkcreate
27        
28    FROM @xrpscan.xrp_ledger.metrics
29    WHERE date >= '2025-01-01'
30      AND metric_fee_total > 0
31      AND metric_transaction_count > 0
32),
33
34transaction_type_stats AS (
35    SELECT 
36        'payment' as transaction_type,
37        AVG(avg_fee_per_transaction) as avg_fee_per_tx,
38        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction) as median_fee_per_tx,
39        SUM(type_payment) as total_transactions,
40        COUNT(*) as days_with_data
41    FROM daily_fees_by_type
42    WHERE type_payment > 0
43    
44    UNION ALL
45    
46    SELECT 'offercreate', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_offercreate), COUNT(*) FROM daily_fees_by_type WHERE type_offercreate > 0
47    UNION ALL
48    SELECT 'trustset', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_trustset), COUNT(*) FROM daily_fees_by_type WHERE type_trustset > 0
49    UNION ALL
50    SELECT 'offercancel', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_offercancel), COUNT(*) FROM daily_fees_by_type WHERE type_offercancel > 0
51    UNION ALL
52    SELECT 'nftokenmint', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_nftokenmint), COUNT(*) FROM daily_fees_by_type WHERE type_nftokenmint > 0
53    UNION ALL
54    SELECT 'nftokenacceptoffer', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_nftokenacceptoffer), COUNT(*) FROM daily_fees_by_type WHERE type_nftokenacceptoffer > 0
55    UNION ALL
56    SELECT 'ammdeposit', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_ammdeposit), COUNT(*) FROM daily_fees_by_type WHERE type_ammdeposit > 0
57    UNION ALL
58    SELECT 'ammwithdraw', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_ammwithdraw), COUNT(*) FROM daily_fees_by_type WHERE type_ammwithdraw > 0
59    UNION ALL
60    SELECT 'accountset', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_accountset), COUNT(*) FROM daily_fees_by_type WHERE type_accountset > 0
61    UNION ALL
62    SELECT 'checkcreate', AVG(avg_fee_per_transaction), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction), SUM(type_checkcreate), COUNT(*) FROM daily_fees_by_type WHERE type_checkcreate > 0
63)
64
65SELECT 
66    transaction_type,
67    ROUND(avg_fee_per_tx, 6) as avg_fee_per_transaction,
68    ROUND(median_fee_per_tx, 6) as median_fee_per_transaction,
69    total_transactions,
70    days_with_data
71FROM transaction_type_stats
72ORDER BY total_transactions DESC;