-- Average transaction fees for top 10 transaction types 
-- (Note: True median requires individual transaction data, not available in aggregated metrics)
WITH daily_fees_by_type AS (
    SELECT 
        date::date,
        metric_fee_total,
        metric_transaction_count,
        
        -- Calculate estimated fee allocation per transaction type
        -- Assumes equal fees across transaction types (limitation of aggregated data)
        CASE 
            WHEN metric_transaction_count > 0 
            THEN (metric_fee_total / metric_transaction_count) 
            ELSE 0 
        END as avg_fee_per_transaction,
        
        type_payment,
        type_offercreate,
        type_trustset,
        type_offercancel,
        type_nftokenmint,
        type_nftokenacceptoffer,
        type_ammdeposit,
        type_ammwithdraw,
        type_accountset,
        type_checkcreate
        
    FROM @xrpscan.xrp_ledger.metrics
    WHERE date >= '2025-01-01'
      AND metric_fee_total > 0
      AND metric_transaction_count > 0
),

transaction_type_stats AS (
    SELECT 
        'payment' as transaction_type,
        AVG(avg_fee_per_transaction) as avg_fee_per_tx,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_fee_per_transaction) as median_fee_per_tx,
        SUM(type_payment) as total_transactions,
        COUNT(*) as days_with_data
    FROM daily_fees_by_type
    WHERE type_payment > 0
    
    UNION ALL
    
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
    UNION ALL
    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
)

SELECT 
    transaction_type,
    ROUND(avg_fee_per_tx, 6) as avg_fee_per_transaction,
    ROUND(median_fee_per_tx, 6) as median_fee_per_transaction,
    total_transactions,
    days_with_data
FROM transaction_type_stats
ORDER BY total_transactions DESC;