Payment Gateway Analytics & Fraud Detection

Project Objective: To analyze high-volume transaction data to optimize payment success rates, monitor merchant integration health, and detect anomalous transaction patterns indicative of card-testing fraud.

Core Technologies: PostgreSQL, Advanced SQL (CTEs, Window Functions, Time-Series Analysis).

1. Schema Design

The relational database is normalized into three core tables:

users: Tracks customer demographics and account status.
merchants: Stores business integration types and industry classifications.
transactions: The fact table logging every payment attempt, amount, timestamp, and status.

2. Business Logic & Code Explanations

A. Monitoring Merchant Performance & API Health

The Problem: The business needs to know which merchants are experiencing high failure rates so the technical integration team can proactively troubleshoot.

WITH MerchantStats AS (
    SELECT 
        m.merchant_name,
        m.industry,
        COUNT(t.transaction_id) AS total_transactions,
        SUM(CASE WHEN t.status = 'Success' THEN 1 ELSE 0 END) AS successful_transactions,
        SUM(CASE WHEN t.status = 'Success' THEN t.amount ELSE 0 END) AS total_revenue_usd
    FROM transactions t
    JOIN merchants m ON t.merchant_id = m.merchant_id
    WHERE t.transaction_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3 months')
    GROUP BY m.merchant_name, m.industry
)
SELECT 
    merchant_name,
    industry,
    total_transactions,
    total_revenue_usd,
    ROUND((successful_transactions * 100.0) / NULLIF(total_transactions, 0), 2) AS success_rate_percentage
FROM MerchantStats
WHERE total_transactions > 100 -- Filtering out statistical noise from new merchants
ORDER BY success_rate_percentage ASC;

WITH MerchantStats AS (
    SELECT 
        m.merchant_name,
        m.industry,
        COUNT(t.transaction_id) AS total_transactions,
        SUM(CASE WHEN t.status = 'Success' THEN 1 ELSE 0 END) AS successful_transactions,
        SUM(CASE WHEN t.status = 'Success' THEN t.amount ELSE 0 END) AS total_revenue_usd
    FROM transactions t
    JOIN merchants m ON t.merchant_id = m.merchant_id
    WHERE t.transaction_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3 months')
    GROUP BY m.merchant_name, m.industry
)
SELECT 
    merchant_name,
    industry,
    total_transactions,
    total_revenue_usd,
    ROUND((successful_transactions * 100.0) / NULLIF(total_transactions, 0), 2) AS success_rate_percentage
FROM MerchantStats
WHERE total_transactions > 100 -- Filtering out statistical noise from new merchants
ORDER BY success_rate_percentage ASC;

Code Explanation: I utilized a Common Table Expression (CTE) to first aggregate the raw transactional data. Conditional aggregation (SUM(CASE WHEN...)) was used to isolate successful transactions without needing multiple subqueries. The NULLIF function in the final SELECT statement acts as a safeguard against divide-by-zero errors.

B. Detecting Anomalous Transaction Spikes (Fraud Risk)

The Problem: “Card testing” is a common fraud vector where bad actors run dozens of small transactions rapidly. We need to flag users whose daily volume spikes abnormally compared to their historical baseline.

WITH DailyUserVolume AS (
    SELECT 
        user_id,
        DATE(transaction_date) AS txn_date,
        COUNT(transaction_id) AS daily_txn_count
    FROM transactions
    WHERE status = 'Success'
    GROUP BY user_id, DATE(transaction_date)
),
MovingAverages AS (
    SELECT 
        user_id,
        txn_date,
        daily_txn_count,
        AVG(daily_txn_count) OVER (
            PARTITION BY user_id 
            ORDER BY txn_date 
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS avg_7d_txn_count
    FROM DailyUserVolume
)
SELECT 
    user_id,
    txn_date,
    daily_txn_count,
    ROUND(avg_7d_txn_count, 2) as baseline_avg,
    ROUND((daily_txn_count - avg_7d_txn_count) / NULLIF(avg_7d_txn_count, 0) * 100, 2) AS spike_percentage
FROM MovingAverages
WHERE daily_txn_count > (avg_7d_txn_count * 3) -- Flags volume > 300% of baseline
  AND daily_txn_count > 5; -- Ignores standard low-volume variance

WITH DailyUserVolume AS (
    SELECT 
        user_id,
        DATE(transaction_date) AS txn_date,
        COUNT(transaction_id) AS daily_txn_count
    FROM transactions
    WHERE status = 'Success'
    GROUP BY user_id, DATE(transaction_date)
),
MovingAverages AS (
    SELECT 
        user_id,
        txn_date,
        daily_txn_count,
        AVG(daily_txn_count) OVER (
            PARTITION BY user_id 
            ORDER BY txn_date 
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS avg_7d_txn_count
    FROM DailyUserVolume
)
SELECT 
    user_id,
    txn_date,
    daily_txn_count,
    ROUND(avg_7d_txn_count, 2) as baseline_avg,
    ROUND((daily_txn_count - avg_7d_txn_count) / NULLIF(avg_7d_txn_count, 0) * 100, 2) AS spike_percentage
FROM MovingAverages
WHERE daily_txn_count > (avg_7d_txn_count * 3) -- Flags volume > 300% of baseline
  AND daily_txn_count > 5; -- Ignores standard low-volume variance

Code Explanation: This query relies on an advanced Window Function (AVG() OVER). I partitioned the data by user_id and ordered it chronologically. The ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING clause calculates a rolling 7-day average excluding the current day, creating a dynamic historical baseline to compare against the current day’s volume.

C. Customer Retention via Monthly Cohort Analysis

The Problem: The marketing and growth teams need to understand the “drop-off” rate of new users. Do users who signed up in January keep transacting in February and March, or do they abandon the platform?

WITH FirstTransaction AS (
    -- Step 1: Find the first month a user ever made a successful transaction
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(transaction_date)) AS cohort_month
    FROM transactions
    WHERE status = 'Success'
    GROUP BY user_id
),
MonthlyActivity AS (
    -- Step 2: Find every distinct month a user made a successful transaction
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', transaction_date) AS activity_month
    FROM transactions
    WHERE status = 'Success'
)
-- Step 3: Join and calculate the month offset
SELECT 
    f.cohort_month,
    -- Safely calculate month difference even if it crosses into a new year
    (EXTRACT(YEAR FROM a.activity_month) - EXTRACT(YEAR FROM f.cohort_month)) * 12 + 
    (EXTRACT(MONTH FROM a.activity_month) - EXTRACT(MONTH FROM f.cohort_month)) AS month_number,
    COUNT(DISTINCT a.user_id) AS active_users
FROM FirstTransaction f
JOIN MonthlyActivity a ON f.user_id = a.user_id
GROUP BY f.cohort_month, month_number
ORDER BY f.cohort_month, month_number;

WITH FirstTransaction AS (
    -- Step 1: Find the first month a user ever made a successful transaction
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(transaction_date)) AS cohort_month
    FROM transactions
    WHERE status = 'Success'
    GROUP BY user_id
),
MonthlyActivity AS (
    -- Step 2: Find every distinct month a user made a successful transaction
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', transaction_date) AS activity_month
    FROM transactions
    WHERE status = 'Success'
)
-- Step 3: Join and calculate the month offset
SELECT 
    f.cohort_month,
    -- Safely calculate month difference even if it crosses into a new year
    (EXTRACT(YEAR FROM a.activity_month) - EXTRACT(YEAR FROM f.cohort_month)) * 12 + 
    (EXTRACT(MONTH FROM a.activity_month) - EXTRACT(MONTH FROM f.cohort_month)) AS month_number,
    COUNT(DISTINCT a.user_id) AS active_users
FROM FirstTransaction f
JOIN MonthlyActivity a ON f.user_id = a.user_id
GROUP BY f.cohort_month, month_number
ORDER BY f.cohort_month, month_number;

Code Explanation: This query performs a classic Cohort Analysis.

The first CTE (FirstTransaction) establishes the “Cohort Month” for each user based on their very first successful payment.
The second CTE (MonthlyActivity) creates a timeline of all subsequent active months for those users.
The final SELECT joins these together and uses EXTRACT math to calculate the exact month_number (Month 0 is their first month, Month 1 is the following month, etc.).
Robustness Note: Instead of just using EXTRACT(MONTH FROM AGE()), the year difference is explicitly multiplied by 12. This ensures the calculation remains perfectly accurate even if a user’s retention spans across multiple calendar years.

Click here to access the GitHub Repo for this