03_analytics.sql

-- ============================================================
--  WAVEPAY FINTECH — ANALYTICAL INTELLIGENCE PLATFORM
--  12 Business-Critical SQL Queries
--
--  Author  : Arogundade Caleb Oluwadamilola | TCG Analytics
--  Engine  : PostgreSQL 14+
--  File    : 03_analytics.sql
--
--  Covers:
--   Q01  Monthly Volume & Revenue Trend with MoM Growth
--   Q02  Channel Performance Deep-Dive (volume, fees, failures)
--   Q03  Merchant Pareto — Top 20% Revenue Concentration
--   Q04  Customer Spend Tiers (RFM-style NTILE segmentation)
--   Q05  Wallet Lifecycle Cohort Analysis
--   Q06  Cross-Border Remittance Corridor Intelligence
--   Q07  Fraud Pattern Profiling (time, amount, risk tier)
--   Q08  Failed Transaction Forensics — Revenue Leakage
--   Q09  CBN Regulatory Compliance — Large Value Reporting
--   Q10  Settlement SLA Performance — Merchant Tier Breach
--   Q11  Customer Lifetime Value Ranking (Window Functions)
--   Q12  Executive KPI Dashboard — Platform Health Summary
-- ============================================================


-- ════════════════════════════════════════════════════════════
--  QUERY 01
--  MONTHLY TRANSACTION VOLUME & REVENUE TREND
--  Techniques: DATE_TRUNC, LAG(), SUM() OVER, FILTER
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  How is platform transaction volume and fee revenue trending
  month-over-month? Where are we accelerating or declining?
*/

WITH monthly_metrics AS (
    SELECT
        DATE_TRUNC('month', initiated_at)             AS txn_month,
        currency_code,
        COUNT(*)                                      AS total_txns,
        COUNT(*) FILTER (WHERE status = 'Successful') AS successful_txns,
        COUNT(*) FILTER (WHERE status = 'Failed')     AS failed_txns,
        ROUND(SUM(amount)
              FILTER (WHERE status = 'Successful'), 2) AS gross_volume,
        ROUND(SUM(fee)
              FILTER (WHERE status = 'Successful'), 2) AS total_fees,
        ROUND(SUM(vat)
              FILTER (WHERE status = 'Successful'), 2) AS total_vat,
        ROUND(AVG(amount)
              FILTER (WHERE status = 'Successful'), 2) AS avg_txn_size,
        ROUND(AVG(processing_ms)
              FILTER (WHERE status = 'Successful'), 0) AS avg_processing_ms
    FROM transactions
    GROUP BY DATE_TRUNC('month', initiated_at), currency_code
),
with_growth AS (
    SELECT
        txn_month,
        currency_code,
        total_txns,
        successful_txns,
        failed_txns,
        gross_volume,
        total_fees,
        avg_txn_size,
        avg_processing_ms,
        -- Month-over-month growth using LAG
        LAG(gross_volume) OVER (
            PARTITION BY currency_code
            ORDER BY txn_month
        )                                             AS prev_month_volume,
        LAG(total_fees) OVER (
            PARTITION BY currency_code
            ORDER BY txn_month
        )                                             AS prev_month_fees,
        -- Cumulative YTD revenue
        SUM(total_fees) OVER (
            PARTITION BY currency_code,
                         DATE_TRUNC('year', txn_month)
            ORDER BY txn_month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )                                             AS ytd_fees,
        -- 3-month rolling average volume
        ROUND(AVG(gross_volume) OVER (
            PARTITION BY currency_code
            ORDER BY txn_month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2)                                         AS rolling_3m_avg_volume
    FROM monthly_metrics
)
SELECT
    TO_CHAR(txn_month, 'Mon YYYY')                    AS month,
    currency_code,
    total_txns,
    successful_txns,
    failed_txns,
    ROUND(100.0 * failed_txns / NULLIF(total_txns,0),1) AS failure_rate_pct,
    gross_volume,
    total_fees,
    avg_txn_size,
    avg_processing_ms                                 AS avg_ms,
    ROUND(
        100.0 * (gross_volume - prev_month_volume)
        / NULLIF(prev_month_volume, 0), 1
    )                                                 AS mom_volume_growth_pct,
    ROUND(
        100.0 * (total_fees - prev_month_fees)
        / NULLIF(prev_month_fees, 0), 1
    )                                                 AS mom_fee_growth_pct,
    rolling_3m_avg_volume,
    ytd_fees
FROM with_growth
ORDER BY currency_code, txn_month DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 02
--  CHANNEL PERFORMANCE DEEP-DIVE
--  Techniques: FILTER aggregation, RANK(), ROUND, CASE
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Which payment channels drive the most volume and revenue?
  Where are we losing money to failures and poor UX?
*/

SELECT
    channel,
    COUNT(*)                                          AS total_txns,
    COUNT(*) FILTER (WHERE status = 'Successful')     AS successful_txns,
    COUNT(*) FILTER (WHERE status = 'Failed')         AS failed_txns,
    COUNT(*) FILTER (WHERE status = 'Pending')        AS pending_txns,
    COUNT(*) FILTER (WHERE status = 'Reversed')       AS reversed_txns,

    ROUND(SUM(amount)
          FILTER (WHERE status = 'Successful'), 2)    AS volume_ngn,
    ROUND(SUM(fee)
          FILTER (WHERE status = 'Successful'), 2)    AS fee_revenue_ngn,
    ROUND(AVG(amount)
          FILTER (WHERE status = 'Successful'), 2)    AS avg_txn_size,
    ROUND(AVG(processing_ms)
          FILTER (WHERE status = 'Successful'), 0)    AS avg_processing_ms,

    -- Failure rate
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status = 'Failed')
        / NULLIF(COUNT(*), 0), 2
    )                                                 AS failure_rate_pct,

    -- Revenue lost to failed transactions
    ROUND(SUM(amount)
          FILTER (WHERE status = 'Failed'), 2)        AS lost_volume_failed,

    -- Volume market share within platform
    ROUND(
        100.0 * SUM(amount) FILTER (WHERE status = 'Successful')
        / SUM(SUM(amount) FILTER (WHERE status = 'Successful')) OVER (), 2
    )                                                 AS vol_share_pct,

    -- Rank by volume
    RANK() OVER (
        ORDER BY SUM(amount) FILTER (WHERE status = 'Successful') DESC
    )                                                 AS volume_rank,

    -- Channel classification
    CASE
        WHEN channel IN ('API', 'Web')          THEN 'Digital — High Value'
        WHEN channel IN ('Mobile_App')          THEN 'Digital — Consumer'
        WHEN channel IN ('POS', 'Bank_Transfer')THEN 'Traditional'
        WHEN channel = 'USSD'                   THEN 'Feature Phone / Rural'
        WHEN channel = 'WhatsApp_Pay'           THEN 'Social Commerce'
        ELSE 'Other'
    END                                               AS channel_segment

FROM transactions
GROUP BY channel
ORDER BY volume_ngn DESC NULLS LAST;


-- ════════════════════════════════════════════════════════════
--  QUERY 03
--  MERCHANT PARETO ANALYSIS — REVENUE CONCENTRATION
--  Techniques: NTILE(), SUM() OVER, cumulative %, JOIN
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Do 20% of merchants drive 80% of our revenue?
  Which merchants are mission-critical to protect?
*/

WITH merchant_revenue AS (
    SELECT
        m.merchant_id,
        m.business_name,
        m.business_category,
        m.account_tier,
        m.country_code,
        m.mdr_rate,
        COUNT(t.txn_id)                               AS total_txns,
        ROUND(SUM(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2) AS gross_volume,
        ROUND(SUM(t.fee)
              FILTER (WHERE t.status = 'Successful'), 2) AS fees_earned,
        ROUND(AVG(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2) AS avg_txn
    FROM merchants      m
    LEFT JOIN transactions t ON m.merchant_id = t.merchant_id
    GROUP BY m.merchant_id, m.business_name, m.business_category,
             m.account_tier, m.country_code, m.mdr_rate
),
ranked AS (
    SELECT
        *,
        RANK() OVER (ORDER BY gross_volume DESC NULLS LAST)    AS revenue_rank,
        NTILE(5) OVER (ORDER BY gross_volume DESC NULLS LAST)  AS quintile,
        -- Cumulative volume percentage (Pareto)
        ROUND(
            100.0 * SUM(gross_volume) OVER (
                ORDER BY gross_volume DESC NULLS LAST
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) / NULLIF(SUM(gross_volume) OVER (), 0), 2
        )                                                      AS cumulative_vol_pct,
        -- Revenue share
        ROUND(
            100.0 * gross_volume
            / NULLIF(SUM(gross_volume) OVER (), 0), 4
        )                                                      AS vol_share_pct
    FROM merchant_revenue
    WHERE gross_volume IS NOT NULL
)
SELECT
    revenue_rank,
    business_name,
    business_category,
    account_tier,
    country_code,
    total_txns,
    gross_volume,
    fees_earned,
    avg_txn,
    vol_share_pct,
    cumulative_vol_pct,
    CASE quintile
        WHEN 1 THEN 'Top 20% — CRITICAL'
        WHEN 2 THEN 'Upper-Mid 21–40%'
        WHEN 3 THEN 'Mid 41–60%'
        WHEN 4 THEN 'Lower-Mid 61–80%'
        WHEN 5 THEN 'Bottom 20%'
    END                                                       AS merchant_tier_label,
    -- Pareto flag
    CASE WHEN cumulative_vol_pct <= 80.00
         THEN '🔑 Pareto Core (80% Revenue)'
         ELSE 'Tail Merchant'
    END                                                       AS pareto_flag
FROM ranked
ORDER BY revenue_rank;


-- ════════════════════════════════════════════════════════════
--  QUERY 04
--  CUSTOMER SPEND SEGMENTATION — RFM-STYLE
--  Techniques: NTILE(), multiple window partitions,
--              subquery, CASE tiering
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Who are our highest-value customers?
  How do we segment them for retention and upsell campaigns?
*/

WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        c.wallet_tier,
        c.kyc_level,
        c.country_code,
        c.referral_source,
        c.status,
        -- Recency: days since last successful transaction
        EXTRACT(DAY FROM NOW() - MAX(t.initiated_at)
                FILTER (WHERE t.status = 'Successful'))::INTEGER AS recency_days,
        -- Frequency
        COUNT(t.txn_id) FILTER (WHERE t.status = 'Successful') AS frequency,
        -- Monetary
        ROUND(SUM(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)         AS monetary_ngn,
        ROUND(SUM(t.fee)
              FILTER (WHERE t.status = 'Successful'), 2)         AS fees_generated,
        ROUND(AVG(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)         AS avg_txn_ngn,
        COUNT(DISTINCT t.channel)
              FILTER (WHERE t.status = 'Successful')             AS channels_used,
        COUNT(t.txn_id) FILTER (WHERE t.status = 'Failed')      AS failed_txns
    FROM customers        c
    LEFT JOIN wallets     w ON c.customer_id = w.customer_id
    LEFT JOIN transactions t ON w.wallet_id  = t.wallet_id
    GROUP BY c.customer_id, c.full_name, c.wallet_tier,
             c.kyc_level, c.country_code, c.referral_source, c.status
    HAVING COUNT(t.txn_id) FILTER (WHERE t.status = 'Successful') > 0
),
scored AS (
    SELECT
        *,
        -- Recency score: lower days = better = higher score
        NTILE(4) OVER (ORDER BY recency_days ASC)          AS r_score,
        -- Frequency score: higher = better
        NTILE(4) OVER (ORDER BY frequency DESC)            AS f_score,
        -- Monetary score: higher = better
        NTILE(4) OVER (ORDER BY monetary_ngn DESC)         AS m_score
    FROM customer_activity
),
rfm AS (
    SELECT
        *,
        r_score + f_score + m_score                        AS rfm_total
    FROM scored
)
SELECT
    full_name,
    country_code,
    wallet_tier,
    kyc_level,
    referral_source,
    recency_days,
    frequency,
    monetary_ngn,
    fees_generated,
    avg_txn_ngn,
    channels_used,
    failed_txns,
    r_score,
    f_score,
    m_score,
    rfm_total,
    -- Segment label
    CASE
        WHEN rfm_total >= 11 THEN '💎 Champions'
        WHEN rfm_total >= 9  THEN '⭐ Loyal Customers'
        WHEN rfm_total >= 7  THEN '🚀 Potential Loyalists'
        WHEN rfm_total >= 5  THEN '⚠️  At Risk'
        ELSE                      '❌ Lost / Inactive'
    END                                                    AS rfm_segment,
    -- Revenue contribution rank
    RANK() OVER (ORDER BY fees_generated DESC)             AS revenue_rank,
    -- Share of total fees
    ROUND(
        100.0 * fees_generated
        / NULLIF(SUM(fees_generated) OVER (), 0), 4
    )                                                      AS fee_share_pct
FROM rfm
ORDER BY rfm_total DESC, monetary_ngn DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 05
--  WALLET LIFECYCLE COHORT ANALYSIS
--  Techniques: CASE classification, FILTER, AVG, COUNT,
--              JOIN across 3 tables
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  What share of wallets are truly active vs dormant vs churned?
  How do utilisation and balance differ across lifecycle stages?
*/

WITH wallet_lifecycle AS (
    SELECT
        w.wallet_id,
        w.currency_code,
        w.balance,
        w.total_funded,
        w.total_debited,
        w.total_txn_count,
        c.wallet_tier,
        c.kyc_level,
        c.country_code,
        c.referral_source,
        EXTRACT(DAY FROM NOW() - w.last_txn_at)::INTEGER   AS days_inactive,
        EXTRACT(DAY FROM NOW() - w.created_at)::INTEGER    AS wallet_age_days,
        -- Lifecycle classification
        CASE
            WHEN w.last_txn_at >= NOW() - INTERVAL '30 days'
                THEN '1 — Active (0–30d)'
            WHEN w.last_txn_at >= NOW() - INTERVAL '90 days'
                THEN '2 — Warm (31–90d)'
            WHEN w.last_txn_at >= NOW() - INTERVAL '180 days'
                THEN '3 — At Risk (91–180d)'
            WHEN w.last_txn_at >= NOW() - INTERVAL '365 days'
                THEN '4 — Dormant (181–365d)'
            WHEN w.last_txn_at IS NOT NULL
                THEN '5 — Churned (365d+)'
            ELSE '6 — Never Transacted'
        END                                                AS lifecycle_stage,
        -- Utilisation rate
        ROUND(
            100.0 * w.total_debited / NULLIF(w.total_funded, 0), 1
        )                                                  AS utilisation_pct
    FROM wallets      w
    JOIN customers    c ON w.customer_id = c.customer_id
)
SELECT
    lifecycle_stage,
    COUNT(*)                                               AS wallet_count,
    ROUND(
        100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1
    )                                                      AS pct_of_total,
    ROUND(AVG(balance), 2)                                 AS avg_balance,
    ROUND(SUM(balance), 2)                                 AS total_balance_float,
    ROUND(AVG(total_funded), 2)                            AS avg_total_funded,
    ROUND(AVG(utilisation_pct), 1)                         AS avg_utilisation_pct,
    ROUND(AVG(total_txn_count), 0)                         AS avg_txn_count,
    ROUND(AVG(days_inactive), 0)                           AS avg_days_inactive,
    ROUND(AVG(wallet_age_days), 0)                         AS avg_wallet_age_days,
    ROUND(AVG(kyc_level), 2)                               AS avg_kyc_level,
    COUNT(*) FILTER (WHERE wallet_tier = 'Business')       AS business_wallets,
    COUNT(*) FILTER (WHERE wallet_tier = 'Premium')        AS premium_wallets,
    COUNT(*) FILTER (WHERE wallet_tier = 'Standard')       AS standard_wallets
FROM wallet_lifecycle
GROUP BY lifecycle_stage
ORDER BY lifecycle_stage;


-- ════════════════════════════════════════════════════════════
--  QUERY 06
--  CROSS-BORDER REMITTANCE CORRIDOR INTELLIGENCE
--  Techniques: corridor grouping, AVG, ROUND, RANK(),
--              PERCENT_RANK(), fee efficiency analysis
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Which remittance corridors move the most money?
  Where is our fee pricing competitive vs the market?
  Which corridors have compliance risk exposure?
*/

WITH corridor_stats AS (
    SELECT
        r.corridor,
        r.sender_country,
        r.receiver_country,
        r.send_currency,
        r.receive_currency,
        -- Volume metrics
        COUNT(*)                                           AS total_remittances,
        COUNT(*) FILTER (WHERE r.status = 'Completed')    AS completed,
        COUNT(*) FILTER (WHERE r.status = 'Failed')       AS failed,
        COUNT(*) FILTER (WHERE r.status = 'Compliance_Hold') AS compliance_holds,
        COUNT(*) FILTER (WHERE r.compliance_flag = TRUE)  AS compliance_flags,
        -- Amount metrics
        ROUND(AVG(r.send_amount), 2)                      AS avg_send_amount,
        ROUND(SUM(r.send_amount)
              FILTER (WHERE r.status = 'Completed'), 2)   AS total_sent,
        ROUND(SUM(r.receive_amount)
              FILTER (WHERE r.status = 'Completed'), 2)   AS total_received,
        -- FX and fee metrics
        ROUND(AVG(r.exchange_rate), 4)                    AS avg_exchange_rate,
        ROUND(AVG(r.fee_usd), 2)                          AS avg_fee_usd,
        ROUND(SUM(r.fee_usd)
              FILTER (WHERE r.status = 'Completed'), 2)   AS total_fee_revenue_usd,
        -- Speed
        ROUND(AVG(r.processing_minutes)
              FILTER (WHERE r.status = 'Completed'), 1)   AS avg_processing_mins,
        MIN(r.processing_minutes)
              FILTER (WHERE r.status = 'Completed')       AS fastest_mins,
        -- Success rate
        ROUND(
            100.0 * COUNT(*) FILTER (WHERE r.status = 'Completed')
            / NULLIF(COUNT(*), 0), 1
        )                                                 AS success_rate_pct
    FROM remittances r
    GROUP BY r.corridor, r.sender_country, r.receiver_country,
             r.send_currency, r.receive_currency
)
SELECT
    corridor,
    send_currency || ' → ' || receive_currency            AS fx_pair,
    total_remittances,
    completed,
    failed,
    compliance_holds,
    compliance_flags,
    success_rate_pct,
    avg_send_amount,
    total_sent,
    total_fee_revenue_usd,
    avg_fee_usd,
    -- Fee as % of average send (cost to sender)
    ROUND(
        100.0 * avg_fee_usd / NULLIF(avg_send_amount, 0), 4
    )                                                     AS fee_pct_of_send,
    avg_exchange_rate,
    avg_processing_mins,
    fastest_mins,
    -- Rankings
    RANK() OVER (ORDER BY total_sent DESC NULLS LAST)     AS volume_rank,
    RANK() OVER (ORDER BY total_fee_revenue_usd DESC NULLS LAST) AS revenue_rank,
    RANK() OVER (ORDER BY avg_processing_mins ASC NULLS LAST)    AS speed_rank,
    -- Compliance risk tier
    CASE
        WHEN compliance_flags >= 3 THEN '🔴 High Compliance Risk'
        WHEN compliance_flags >= 1 THEN '🟡 Moderate Risk'
        ELSE                            '🟢 Low Risk'
    END                                                   AS compliance_tier
FROM corridor_stats
ORDER BY total_sent DESC NULLS LAST;


-- ════════════════════════════════════════════════════════════
--  QUERY 07
--  FRAUD PATTERN PROFILING
--  Techniques: JOIN 3 tables, EXTRACT, CASE bucketing,
--              ROUND, GROUP BY multiple dimensions
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  When, where, and how are fraud events occurring?
  What rules are triggering most, and which are confirmed fraud?
*/

SELECT
    ff.rule_category,
    ff.rule_name,
    -- Time of day classification
    CASE
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 0  AND 5  THEN 'Night (00–05)'
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 6  AND 11 THEN 'Morning (06–11)'
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 12 AND 17 THEN 'Afternoon (12–17)'
        ELSE                                                          'Evening (18–23)'
    END                                                       AS time_of_day,
    -- Transaction amount band
    CASE
        WHEN t.amount <   50000  THEN 'Under ₦50K'
        WHEN t.amount <  200000  THEN '₦50K – ₦200K'
        WHEN t.amount <  500000  THEN '₦200K – ₦500K'
        WHEN t.amount < 1000000  THEN '₦500K – ₦1M'
        WHEN t.amount < 5000000  THEN '₦1M – ₦5M'
        ELSE                          '₦5M+'
    END                                                       AS amount_band,
    ff.risk_tier,
    COUNT(*)                                                  AS flagged_count,
    SUM(ff.confirmed_fraud::INTEGER)                          AS confirmed_fraud_count,
    ROUND(
        100.0 * SUM(ff.confirmed_fraud::INTEGER)
        / NULLIF(COUNT(*), 0), 1
    )                                                         AS confirmation_rate_pct,
    ROUND(AVG(ff.risk_score), 1)                              AS avg_risk_score,
    ROUND(MAX(ff.risk_score), 1)                              AS max_risk_score,
    ROUND(SUM(t.amount), 2)                                   AS total_exposed_amount,
    ROUND(SUM(t.amount)
          FILTER (WHERE ff.confirmed_fraud = TRUE), 2)        AS confirmed_fraud_amount,
    COUNT(*) FILTER (WHERE ff.reviewed = FALSE)               AS pending_review_count,
    -- Avg hours to review (for reviewed items)
    ROUND(
        AVG(EXTRACT(EPOCH FROM (ff.reviewed_at - ff.flagged_at)) / 3600.0)
        FILTER (WHERE ff.reviewed = TRUE), 1
    )                                                         AS avg_review_hours
FROM fraud_flags   ff
JOIN transactions  t  ON ff.txn_id = t.txn_id
JOIN wallets       w  ON ff.wallet_id = w.wallet_id
GROUP BY
    ff.rule_category,
    ff.rule_name,
    CASE
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 0  AND 5  THEN 'Night (00–05)'
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 6  AND 11 THEN 'Morning (06–11)'
        WHEN EXTRACT(HOUR FROM ff.flagged_at) BETWEEN 12 AND 17 THEN 'Afternoon (12–17)'
        ELSE                                                          'Evening (18–23)'
    END,
    CASE
        WHEN t.amount <   50000  THEN 'Under ₦50K'
        WHEN t.amount <  200000  THEN '₦50K – ₦200K'
        WHEN t.amount <  500000  THEN '₦200K – ₦500K'
        WHEN t.amount < 1000000  THEN '₦500K – ₦1M'
        WHEN t.amount < 5000000  THEN '₦1M – ₦5M'
        ELSE                          '₦5M+'
    END,
    ff.risk_tier
ORDER BY avg_risk_score DESC, confirmed_fraud_count DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 08
--  FAILED TRANSACTION FORENSICS — REVENUE LEAKAGE
--  Techniques: FILTER, GROUP BY, RANK(), revenue leakage calc
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Where exactly are transactions failing, and how much
  potential revenue are we losing per failure reason?
*/

WITH failure_detail AS (
    SELECT
        channel,
        txn_type,
        failure_reason,
        currency_code,
        COUNT(*)                                           AS failure_count,
        ROUND(SUM(amount), 2)                              AS lost_volume,
        ROUND(SUM(fee), 2)                                 AS lost_fee_revenue,
        ROUND(AVG(amount), 2)                              AS avg_failed_amount,
        ROUND(AVG(processing_ms), 0)                       AS avg_processing_ms
    FROM transactions
    WHERE status = 'Failed'
      AND failure_reason IS NOT NULL
    GROUP BY channel, txn_type, failure_reason, currency_code
),
channel_failure_totals AS (
    SELECT
        channel,
        SUM(failure_count)                                 AS channel_total_failures,
        SUM(lost_volume)                                   AS channel_lost_volume
    FROM failure_detail
    GROUP BY channel
)
SELECT
    fd.channel,
    fd.txn_type,
    fd.failure_reason,
    fd.currency_code,
    fd.failure_count,
    fd.lost_volume,
    fd.lost_fee_revenue,
    fd.avg_failed_amount,
    fd.avg_processing_ms,
    -- Failure share within channel
    ROUND(
        100.0 * fd.failure_count
        / NULLIF(cft.channel_total_failures, 0), 1
    )                                                      AS pct_of_channel_failures,
    -- Lost volume share within channel
    ROUND(
        100.0 * fd.lost_volume
        / NULLIF(cft.channel_lost_volume, 0), 1
    )                                                      AS pct_of_channel_lost_vol,
    -- Platform-wide failure rank
    RANK() OVER (ORDER BY fd.lost_volume DESC)             AS global_leakage_rank,
    -- Rank within channel
    RANK() OVER (
        PARTITION BY fd.channel
        ORDER BY fd.failure_count DESC
    )                                                      AS rank_in_channel,
    -- Priority flag
    CASE
        WHEN fd.failure_reason = 'Network Timeout'  THEN '🔧 Infrastructure Fix'
        WHEN fd.failure_reason = 'Insufficient Balance' THEN '💬 UX / Nudge Alert'
        WHEN fd.failure_reason = 'Daily Limit Exceeded' THEN '⚙️  Limit Review'
        WHEN fd.failure_reason = 'Invalid PIN'       THEN '🔐 Auth Improvement'
        WHEN fd.failure_reason = 'Card Declined'     THEN '🏦 Bank Integration Fix'
        WHEN fd.failure_reason = 'Bank Downtime'     THEN '🔄 Retry Routing'
        ELSE                                              'Investigate'
    END                                                    AS recommended_action
FROM failure_detail         fd
JOIN channel_failure_totals cft ON fd.channel = cft.channel
ORDER BY fd.lost_volume DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 09
--  CBN REGULATORY COMPLIANCE — LARGE VALUE REPORTING
--  Techniques: Subquery, SUM FILTER, CASE, JOIN 4 tables,
--              regulatory threshold logic
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Which wallets breach CBN's Suspicious Transaction Report (STR)
  threshold of ₦5M single transaction, or the Currency
  Transaction Report (CTR) of ₦10M daily aggregate?
  Do these customers have adequate KYC?
*/

WITH daily_wallet_activity AS (
    SELECT
        t.wallet_id,
        t.txn_date::DATE                                   AS activity_date,
        COUNT(*)                                           AS daily_txn_count,
        ROUND(SUM(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)   AS daily_volume,
        ROUND(MAX(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)   AS single_largest_txn,
        ARRAY_AGG(DISTINCT t.channel)
              FILTER (WHERE t.status = 'Successful')       AS channels_used,
        ARRAY_AGG(DISTINCT t.txn_type)
              FILTER (WHERE t.status = 'Successful')       AS txn_types
    FROM transactions t
    WHERE t.currency_code = 'NGN'
    GROUP BY t.wallet_id, t.txn_date::DATE
),
flagged_activity AS (
    SELECT
        dwa.*,
        w.customer_id,
        c.full_name,
        c.kyc_level,
        c.wallet_tier,
        c.status     AS customer_status,
        c.bvn_verified,
        -- CBN classification
        CASE
            WHEN dwa.single_largest_txn >= 5000000
                 THEN 'STR — Single Transaction ≥ ₦5M'
            WHEN dwa.daily_volume >= 10000000
                 THEN 'CTR — Daily Aggregate ≥ ₦10M'
            WHEN dwa.daily_volume >= 5000000
                 THEN 'Watch — Daily Volume ₦5M–₦10M'
            ELSE NULL
        END                                                AS cbn_flag_type,
        -- Regulatory risk level
        CASE
            WHEN dwa.single_largest_txn >= 5000000
                 AND c.kyc_level < 3                       THEN '🔴 CRITICAL — Low KYC, High Value'
            WHEN dwa.daily_volume >= 10000000              THEN '🔴 CRITICAL — CTR Required'
            WHEN dwa.single_largest_txn >= 5000000         THEN '🟠 HIGH — STR Required'
            WHEN dwa.daily_volume >= 5000000               THEN '🟡 WATCH — Approaching Threshold'
            ELSE NULL
        END                                                AS regulatory_risk_level
    FROM daily_wallet_activity  dwa
    JOIN wallets                w   ON dwa.wallet_id  = w.wallet_id
    JOIN customers              c   ON w.customer_id  = c.customer_id
    WHERE dwa.daily_volume >= 5000000
       OR dwa.single_largest_txn >= 5000000
)
SELECT
    activity_date,
    full_name,
    wallet_id,
    kyc_level,
    wallet_tier,
    bvn_verified,
    customer_status,
    daily_txn_count,
    daily_volume,
    single_largest_txn,
    channels_used,
    txn_types,
    cbn_flag_type,
    regulatory_risk_level,
    -- Action recommendation
    CASE
        WHEN kyc_level < 3 AND single_largest_txn >= 5000000
            THEN 'IMMEDIATE KYC UPGRADE REQUIRED — Block further high-value txns'
        WHEN daily_volume >= 10000000
            THEN 'SUBMIT CTR TO CBN WITHIN 24HRS'
        WHEN single_largest_txn >= 5000000
            THEN 'FILE STR — VERIFY TRANSACTION PURPOSE'
        ELSE 'ENHANCED DUE DILIGENCE — Monitor for 30 days'
    END                                                    AS compliance_action
FROM flagged_activity
WHERE cbn_flag_type IS NOT NULL
ORDER BY single_largest_txn DESC NULLS LAST, daily_volume DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 10
--  SETTLEMENT SLA PERFORMANCE & BREACH ANALYSIS
--  Techniques: JOIN, CASE SLA logic, EXTRACT, FILTER,
--              ROUND, GROUP BY tier
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Are we meeting our merchant settlement SLA commitments?
  (Enterprise=T+1, Growth=T+2, Starter=T+3)
  How much pending float is sitting unresolved?
*/

WITH settlement_analysis AS (
    SELECT
        sl.settlement_id,
        sl.merchant_id,
        m.business_name,
        m.business_category,
        m.account_tier,
        b.bank_name,
        sl.period_start,
        sl.period_end,
        sl.total_transactions,
        sl.gross_amount,
        sl.mdr_deducted,
        sl.vat_deducted,
        sl.net_payable,
        sl.currency_code,
        sl.status,
        sl.created_at,
        sl.settled_at,
        sl.days_to_settle,
        -- SLA commitment by tier
        CASE m.account_tier
            WHEN 'Enterprise' THEN 1
            WHEN 'Growth'     THEN 2
            WHEN 'Starter'    THEN 3
        END                                                AS sla_days_committed,
        -- Days over SLA (only for settled)
        CASE
            WHEN sl.settled_at IS NOT NULL
            THEN GREATEST(
                0,
                sl.days_to_settle - CASE m.account_tier
                    WHEN 'Enterprise' THEN 1
                    WHEN 'Growth'     THEN 2
                    WHEN 'Starter'    THEN 3
                END
            )
        END                                                AS days_over_sla,
        -- Pending duration (for unsettled)
        CASE
            WHEN sl.settled_at IS NULL
            THEN EXTRACT(DAY FROM NOW() - sl.created_at)::INTEGER
        END                                                AS pending_days,
        -- SLA breach flag
        CASE
            WHEN sl.settled_at IS NOT NULL
             AND sl.days_to_settle > CASE m.account_tier
                                         WHEN 'Enterprise' THEN 1
                                         WHEN 'Growth'     THEN 2
                                         WHEN 'Starter'    THEN 3
                                     END
            THEN TRUE
            ELSE FALSE
        END                                                AS sla_breached
    FROM settlement_ledger  sl
    JOIN merchants          m  ON sl.merchant_id     = m.merchant_id
    LEFT JOIN banks         b  ON sl.settlement_bank_id = b.bank_id
)
SELECT
    account_tier,
    COUNT(*)                                               AS total_settlements,
    COUNT(*) FILTER (WHERE status = 'Settled')             AS settled_count,
    COUNT(*) FILTER (WHERE status = 'Pending')             AS pending_count,
    COUNT(*) FILTER (WHERE status = 'Processing')          AS processing_count,
    COUNT(*) FILTER (WHERE status = 'Failed')              AS failed_count,
    -- Financial totals
    ROUND(SUM(gross_amount) / 1e9, 4)                      AS total_gross_B,
    ROUND(SUM(net_payable) / 1e9, 4)                       AS total_net_B,
    ROUND(SUM(mdr_deducted) / 1e6, 2)                      AS total_mdr_M,
    -- Pending float (money owed but not yet paid)
    ROUND(SUM(net_payable)
          FILTER (WHERE status IN ('Pending','Processing'))
          / 1e6, 2)                                        AS pending_float_M,
    -- SLA performance
    MAX(sla_days_committed)                                AS sla_commitment_days,
    ROUND(AVG(days_to_settle)
          FILTER (WHERE status = 'Settled'), 1)             AS avg_days_to_settle,
    COUNT(*) FILTER (WHERE sla_breached = TRUE)            AS sla_breach_count,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE sla_breached = TRUE)
        / NULLIF(COUNT(*) FILTER (WHERE status = 'Settled'), 0), 1
    )                                                      AS breach_rate_pct,
    -- Worst breach
    MAX(days_over_sla)                                     AS max_days_over_sla,
    -- Oldest pending
    MAX(pending_days)                                      AS oldest_pending_days
FROM settlement_analysis
GROUP BY account_tier
ORDER BY
    CASE account_tier
        WHEN 'Enterprise' THEN 1
        WHEN 'Growth'     THEN 2
        WHEN 'Starter'    THEN 3
    END;


-- ════════════════════════════════════════════════════════════
--  QUERY 11
--  CUSTOMER LIFETIME VALUE & REVENUE RANKING
--  Techniques: Multiple window functions (RANK, DENSE_RANK,
--              PERCENT_RANK, CUME_DIST, ROW_NUMBER),
--              LEAD, LAG, running totals
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  Who are our most valuable customers by lifetime fee contribution?
  What share of revenue is each customer responsible for?
  Who moved up or down in the rankings?
*/

WITH customer_ltv AS (
    SELECT
        c.customer_id,
        c.full_name,
        c.country_code,
        c.wallet_tier,
        c.kyc_level,
        c.registered_at,
        EXTRACT(
            DAY FROM NOW() - c.registered_at
        )::INTEGER / 30                                    AS months_on_platform,
        COUNT(t.txn_id) FILTER (WHERE t.status = 'Successful')   AS successful_txns,
        ROUND(SUM(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)          AS lifetime_volume,
        ROUND(SUM(t.fee)
              FILTER (WHERE t.status = 'Successful'), 2)          AS lifetime_fees,
        ROUND(SUM(t.vat)
              FILTER (WHERE t.status = 'Successful'), 2)          AS lifetime_vat,
        ROUND(AVG(t.amount)
              FILTER (WHERE t.status = 'Successful'), 2)          AS avg_txn_size,
        COUNT(DISTINCT DATE_TRUNC('month', t.initiated_at))       AS active_months
    FROM customers         c
    LEFT JOIN wallets      w ON c.customer_id = w.customer_id
    LEFT JOIN transactions t ON w.wallet_id   = t.wallet_id
    GROUP BY c.customer_id, c.full_name, c.country_code,
             c.wallet_tier, c.kyc_level, c.registered_at
    HAVING SUM(t.fee) FILTER (WHERE t.status = 'Successful') > 0
)
SELECT
    full_name,
    country_code,
    wallet_tier,
    kyc_level,
    months_on_platform,
    successful_txns,
    lifetime_volume,
    lifetime_fees,
    avg_txn_size,
    active_months,

    -- Monthly run-rate fee
    ROUND(lifetime_fees / NULLIF(active_months, 0), 2)     AS monthly_fee_run_rate,

    -- Ranking functions — multiple perspectives
    RANK()        OVER (ORDER BY lifetime_fees DESC)        AS fee_rank,
    DENSE_RANK()  OVER (ORDER BY lifetime_fees DESC)        AS fee_dense_rank,
    ROW_NUMBER()  OVER (ORDER BY lifetime_fees DESC)        AS row_num,

    -- Relative standing
    ROUND(PERCENT_RANK() OVER (ORDER BY lifetime_fees) * 100, 1)  AS percentile,
    ROUND(CUME_DIST()    OVER (ORDER BY lifetime_fees) * 100, 1)  AS cumulative_dist,

    -- Revenue share
    ROUND(
        100.0 * lifetime_fees / NULLIF(SUM(lifetime_fees) OVER (), 0), 4
    )                                                       AS fee_share_pct,

    -- Cumulative revenue share (top N customers)
    ROUND(
        100.0 * SUM(lifetime_fees) OVER (
            ORDER BY lifetime_fees DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) / NULLIF(SUM(lifetime_fees) OVER (), 0), 2
    )                                                       AS cumulative_fee_share_pct,

    -- Neighbour comparison (who is just ahead and just behind)
    LAG(full_name, 1)  OVER (ORDER BY lifetime_fees DESC)   AS customer_ranked_above,
    LEAD(full_name, 1) OVER (ORDER BY lifetime_fees DESC)   AS customer_ranked_below,
    LAG(lifetime_fees,1) OVER (ORDER BY lifetime_fees DESC) AS fees_of_rank_above

FROM customer_ltv
ORDER BY lifetime_fees DESC;


-- ════════════════════════════════════════════════════════════
--  QUERY 12
--  EXECUTIVE KPI DASHBOARD — PLATFORM HEALTH SUMMARY
--  Techniques: CROSS JOIN LATERAL, multiple subqueries,
--              CTE chain, conditional aggregation
-- ════════════════════════════════════════════════════════════
/*
  Business Question:
  What is the overall health of the WavePay platform?
  Single-query executive summary across all key metrics.
*/

WITH

-- Transaction KPIs
txn_kpis AS (
    SELECT
        COUNT(*)                                           AS total_txns,
        COUNT(*) FILTER (WHERE status = 'Successful')     AS successful_txns,
        COUNT(*) FILTER (WHERE status = 'Failed')         AS failed_txns,
        COUNT(*) FILTER (WHERE status = 'Reversed')       AS reversed_txns,
        COUNT(*) FILTER (WHERE status = 'Flagged')        AS flagged_txns,
        ROUND(SUM(amount)
              FILTER (WHERE status = 'Successful'
                        AND currency_code = 'NGN'), 2)    AS ngn_volume,
        ROUND(SUM(fee)
              FILTER (WHERE status = 'Successful'), 2)    AS total_fee_revenue,
        ROUND(AVG(amount)
              FILTER (WHERE status = 'Successful'
                        AND currency_code = 'NGN'), 2)    AS avg_txn_size_ngn,
        ROUND(AVG(processing_ms)
              FILTER (WHERE status = 'Successful'), 0)    AS avg_processing_ms
    FROM transactions
),

-- Customer KPIs
customer_kpis AS (
    SELECT
        COUNT(*)                                           AS total_customers,
        COUNT(*) FILTER (WHERE status = 'Active')         AS active_customers,
        COUNT(*) FILTER (WHERE status = 'Suspended')      AS suspended_customers,
        COUNT(*) FILTER (WHERE kyc_level = 3)             AS fully_kyc_verified,
        COUNT(*) FILTER (WHERE bvn_verified = TRUE)       AS bvn_verified,
        COUNT(*) FILTER (WHERE wallet_tier = 'Business')  AS business_tier,
        COUNT(*) FILTER (WHERE wallet_tier = 'Premium')   AS premium_tier,
        COUNT(*) FILTER (WHERE wallet_tier = 'Standard')  AS standard_tier
    FROM customers
),

-- Wallet KPIs
wallet_kpis AS (
    SELECT
        COUNT(*)                                           AS total_wallets,
        COUNT(*) FILTER (WHERE status = 'Active')         AS active_wallets,
        COUNT(*) FILTER (WHERE status = 'Dormant')        AS dormant_wallets,
        COUNT(*) FILTER (WHERE status = 'Frozen')         AS frozen_wallets,
        ROUND(SUM(balance), 2)                             AS total_platform_float,
        ROUND(AVG(balance), 2)                             AS avg_wallet_balance,
        ROUND(SUM(total_funded), 2)                        AS total_ever_funded
    FROM wallets
),

-- Remittance KPIs
remittance_kpis AS (
    SELECT
        COUNT(*)                                           AS total_remittances,
        COUNT(*) FILTER (WHERE status = 'Completed')      AS completed_remittances,
        COUNT(*) FILTER (WHERE compliance_flag = TRUE)    AS compliance_flagged,
        COUNT(DISTINCT corridor)                           AS active_corridors,
        ROUND(SUM(send_amount)
              FILTER (WHERE status = 'Completed'), 2)     AS total_sent,
        ROUND(SUM(fee_usd)
              FILTER (WHERE status = 'Completed'), 2)     AS total_remit_fees_usd,
        ROUND(AVG(processing_minutes)
              FILTER (WHERE status = 'Completed'), 1)     AS avg_processing_mins
    FROM remittances
),

-- Fraud KPIs
fraud_kpis AS (
    SELECT
        COUNT(*)                                           AS total_flags,
        COUNT(*) FILTER (WHERE confirmed_fraud = TRUE)    AS confirmed_fraud,
        COUNT(*) FILTER (WHERE reviewed = FALSE)          AS pending_review,
        ROUND(AVG(risk_score), 1)                         AS avg_risk_score,
        COUNT(*) FILTER (WHERE risk_tier = 'Critical')    AS critical_flags
    FROM fraud_flags
),

-- Settlement KPIs
settlement_kpis AS (
    SELECT
        COUNT(*)                                           AS total_settlements,
        COUNT(*) FILTER (WHERE status = 'Settled')        AS settled,
        COUNT(*) FILTER (WHERE status = 'Pending')        AS pending,
        COUNT(*) FILTER (WHERE status = 'Failed')         AS failed,
        ROUND(SUM(net_payable)
              FILTER (WHERE status IN ('Pending','Processing'))
              / 1e9, 4)                                   AS pending_float_B,
        ROUND(SUM(mdr_deducted) / 1e9, 4)                 AS total_mdr_earned_B
    FROM settlement_ledger
),

-- Merchant KPIs
merchant_kpis AS (
    SELECT
        COUNT(*)                                           AS total_merchants,
        COUNT(*) FILTER (WHERE status = 'Active')         AS active_merchants,
        COUNT(*) FILTER (WHERE account_tier = 'Enterprise') AS enterprise,
        COUNT(*) FILTER (WHERE account_tier = 'Growth')   AS growth,
        COUNT(*) FILTER (WHERE account_tier = 'Starter')  AS starter
    FROM merchants
)

-- Final executive summary
SELECT
    -- Transaction Health
    t.total_txns,
    t.successful_txns,
    ROUND(100.0 * t.successful_txns / NULLIF(t.total_txns, 0), 2)  AS success_rate_pct,
    t.failed_txns,
    ROUND(100.0 * t.failed_txns / NULLIF(t.total_txns, 0), 2)      AS failure_rate_pct,
    t.reversed_txns,
    t.flagged_txns,

    -- Volume & Revenue
    t.ngn_volume,
    t.total_fee_revenue,
    t.avg_txn_size_ngn,
    ROUND(t.avg_processing_ms / 1000.0, 2)                         AS avg_processing_secs,

    -- Customers
    c.total_customers,
    c.active_customers,
    c.fully_kyc_verified,
    ROUND(100.0 * c.fully_kyc_verified / NULLIF(c.total_customers,0), 1) AS kyc3_rate_pct,
    c.business_tier,
    c.premium_tier,

    -- Wallets
    w.total_wallets,
    w.active_wallets,
    w.dormant_wallets,
    w.total_platform_float,
    w.avg_wallet_balance,

    -- Remittances
    r.total_remittances,
    r.completed_remittances,
    ROUND(100.0 * r.completed_remittances / NULLIF(r.total_remittances,0), 1) AS remit_success_pct,
    r.active_corridors,
    r.total_sent,
    r.total_remit_fees_usd,
    r.avg_processing_mins                                           AS avg_remit_mins,
    r.compliance_flagged,

    -- Fraud
    f.total_flags,
    f.confirmed_fraud,
    ROUND(100.0 * f.confirmed_fraud / NULLIF(f.total_flags,0), 1)  AS fraud_confirm_rate_pct,
    f.pending_review,
    f.avg_risk_score,
    f.critical_flags,

    -- Settlements
    s.total_settlements,
    s.settled,
    s.pending                                                       AS settlements_pending,
    s.failed                                                        AS settlements_failed,
    s.pending_float_B,
    s.total_mdr_earned_B,

    -- Merchants
    m.total_merchants,
    m.active_merchants,
    m.enterprise,
    m.growth,
    m.starter

FROM txn_kpis        t
CROSS JOIN customer_kpis   c
CROSS JOIN wallet_kpis     w
CROSS JOIN remittance_kpis r
CROSS JOIN fraud_kpis      f
CROSS JOIN settlement_kpis s
CROSS JOIN merchant_kpis   m;