UPI Transaction
Analysis

Business objective: Quantify platform scale and measure operational health.

-- Query 1: Total Transaction Volume & Value
SELECT
    COUNT(transaction_id)          AS total_transactions,
    ROUND(SUM(amount), 2)         AS total_value_inr
FROM upi_transaction_history;

-- Query 2: Success Rate
SELECT
    COUNT(CASE WHEN status = 'success' THEN 1 END) AS successful,
    ROUND(
        COUNT(CASE WHEN status = 'success' THEN 1 END) * 100.0
        / COUNT(transaction_id), 2
    ) AS success_rate_pct
FROM upi_transaction_history;

✅ Result: 1,00,000 transactions | Total value ≈ ₹42.37L | Success rate 92.14%

Business objective: Quantify the portion of transaction volume flagged as fraudulent.

SELECT
    COUNT(CASE WHEN fraud_flag = 1 THEN 1 END) AS fraud_txns,
    ROUND(
        COUNT(CASE WHEN fraud_flag = 1 THEN 1 END) * 100.0
        / COUNT(transaction_id), 2
    ) AS fraud_rate_pct
FROM upi_transaction_history;

✅ Result: 2,000 fraud transactions | Fraud rate 2.00% — above the typical industry benchmark of <0.5%

Business objective: Determine whether rooted devices present significantly higher fraud risk.

SELECT
    d.is_rooted,
    COUNT(t.transaction_id)                          AS total_txns,
    COUNT(CASE WHEN t.fraud_flag = 1 THEN 1 END)     AS fraud_txns,
    ROUND(
        COUNT(CASE WHEN t.fraud_flag = 1 THEN 1 END)
        * 100.0 / COUNT(t.transaction_id), 4
    )                                                  AS fraud_rate_pct
FROM upi_transaction_history t
JOIN device_info d ON t.device_id = d.device_id
GROUP BY d.is_rooted;

🚨 Major finding: Rooted devices → 20.69% fraud rate. Non-rooted → 1.39%. Rooted devices are ~15× higher risk. Confirmed statistically via chi-square (χ² = 5,825.10, p < 0.001).

Business objective: Measure risk operations capacity by tracking unresolved fraud alerts.

SELECT
    COUNT(alert_id)                                                    AS total_alerts,
    COUNT(CASE WHEN resolved = 1 THEN 1 END)                       AS resolved_alerts,
    COUNT(CASE WHEN resolved = 0 THEN 1 END)                       AS open_alerts,
    ROUND(
        COUNT(CASE WHEN resolved = 1 THEN 1 END) * 100.0
        / COUNT(alert_id), 2
    )                                                                  AS resolution_rate_pct
FROM fraud_alert_history;

✅ Result: 2,000 total alerts | 1,752 resolved (87.60%) | 248 still open (12.40%) — backlog requires staffing allocation

Business objective: Measure month-on-month processing volume growth using window functions.

WITH monthly_ttv AS (
    SELECT
        DATE_FORMAT(timestamp, '%Y-%m') AS ym,
        ROUND(SUM(amount), 2)          AS ttv
    FROM upi_transaction_history
    WHERE status = 'success'
    GROUP BY DATE_FORMAT(timestamp, '%Y-%m')
)
SELECT
    ym,
    ttv                                               AS current_month_ttv,
    LAG(ttv) OVER(ORDER BY ym)                   AS prev_month_ttv,
    ROUND(
        (ttv - LAG(ttv) OVER(ORDER BY ym))
        / NULLIF(LAG(ttv) OVER(ORDER BY ym), 0) * 100, 2
    )                                                  AS mom_growth_pct
FROM monthly_ttv
ORDER BY ym;

✅ CTE + LAG window function demonstrates platform growth trajectory. Consistent positive MoM growth across the analysis period confirms healthy adoption.

Business objective: Identify top-spending customers within each region for targeted marketing.

WITH ranked_cust AS (
    SELECT
        c.region,
        c.customer_id,
        c.full_name,
        ROUND(SUM(t.amount), 2)            AS total_spend,
        DENSE_RANK() OVER (
            PARTITION BY c.region
            ORDER BY SUM(t.amount) DESC
        )                                   AS spend_rank
    FROM upi_transaction_history t
    JOIN customer_master c ON t.customer_id = c.customer_id
    WHERE t.status = 'success'
    GROUP BY c.region, c.customer_id, c.full_name
)
SELECT * FROM ranked_cust
WHERE spend_rank <= 3;

✅ Identifies top 3 spenders per region using DENSE_RANK partitioning. Top customers transacted 40+ times and spent over ₹2,000 each — prime targets for loyalty programmes.

Business objective: Create a reusable daily executive KPI view for dashboards.

CREATE OR REPLACE VIEW view_daily_executive_metrics AS
SELECT
    DATE(timestamp)                               AS txn_date,
    COUNT(*)                                       AS total_attempts,
    SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) AS success_count,
    ROUND(
        SUM(CASE WHEN status='success' THEN 1 ELSE 0 END)
        * 100.0 / NULLIF(COUNT(*), 0), 2
    )                                              AS success_rate_pct,
    ROUND(SUM(CASE WHEN status='success' THEN amount ELSE 0 END), 2) AS successful_ttv,
    SUM(CASE WHEN fraud_flag=1 THEN 1 ELSE 0 END)  AS fraud_alerts
FROM upi_transaction_history
GROUP BY DATE(timestamp);

✅ Virtual view pre-aggregates daily KPIs for reporting. Simplifies dashboard queries and enables real-time monitoring when connected to a live data feed.

Business objective: Segment customers into loyalty tiers based on total transaction spend.

WITH customer_spend AS (
    SELECT customer_id, SUM(amount) AS total_spend
    FROM upi_transaction_history
    WHERE status = 'success'
    GROUP BY customer_id
)
SELECT
    CASE
        WHEN total_spend < 100        THEN 'Bronze (Low < ₹100)'
        WHEN total_spend <= 1000       THEN 'Silver (Medium ₹100–1000)'
        ELSE                                 'Gold (High > ₹1000)'
    END                            AS loyalty_tier,
    COUNT(customer_id)             AS customer_count,
    ROUND(COUNT(*)*100.0 /
        (SELECT COUNT(*) FROM customer_master), 2) AS pct_of_customers
FROM customer_spend
GROUP BY loyalty_tier;

✅ Result: 88.29% of customers are Silver tier (₹100–₹1,000 total spend); only 10.21% are Gold spenders. Significant opportunity to drive engagement and loyalty upgrades.