Data Analytics Applications in Fintech Real-World Examples

-- PostgreSQL: users with 10+ transactions in the last hour
SELECT user_id,
       COUNT(*)    AS txn_count,
       SUM(amount) AS total_amount
FROM transactions
WHERE txn_time >= NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) >= 10
ORDER BY txn_count DESC;

-- Payments more than 5x the user's average payment
SELECT txn_id, user_id, amount, user_avg
FROM (
    SELECT txn_id, user_id, amount,
           AVG(amount) OVER (PARTITION BY user_id) AS user_avg
    FROM transactions
    WHERE status = 'SUCCESS'
) t
WHERE amount > 5 * user_avg
ORDER BY amount DESC;

-- Count distinct users reaching each step
SELECT step,
       COUNT(DISTINCT user_id) AS users
FROM loan_events
WHERE event_date >= DATE '2026-10-01'
GROUP BY step
ORDER BY MIN(step_order);

import pandas as pd

steps = ["signup", "kyc_done", "applied", "approved", "disbursed"]
ev = pd.read_csv("loan_events.csv")
users = [ev.loc[ev["step"] == s, "user_id"].nunique() for s in steps]

funnel = pd.DataFrame({"step": steps, "users": users})
funnel["step_conversion_%"] = (funnel["users"] / funnel["users"].shift(1) * 100).round(1)
funnel["overall_%"] = (funnel["users"] / funnel["users"].iloc[0] * 100).round(1)
print(funnel)

-- PostgreSQL: active users per cohort and month number
WITH first_txn AS (
    SELECT user_id, DATE_TRUNC('month', MIN(txn_time)) AS cohort
    FROM transactions
    GROUP BY user_id
),
activity AS (
    SELECT DISTINCT t.user_id, f.cohort,
           DATE_TRUNC('month', t.txn_time) AS active_month
    FROM transactions t
    JOIN first_txn f USING (user_id)
)
SELECT cohort,
       (EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort)) * 12
     + (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort)) AS month_number,
       COUNT(*) AS active_users
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

import pandas as pd

c = pd.read_csv("cohort_counts.csv")  # cohort, month_number, active_users
table = c.pivot(index="cohort", columns="month_number", values="active_users")
retention = (table.div(table[0], axis=0) * 100).round(1)
print(retention)

KPI                 | Definition
--------------------|------------------------------------------
Active users        | Users with >= 1 successful txn in period
Txn success rate    | Successful txns / all attempted txns
Avg txn value       | Total successful amount / successful txns
Flagged txn rate    | Txns flagged by any risk rule / all txns
KYC completion      | Users with KYC done / sign-ups
Month-1 retention   | Cohort users active in month 1 / cohort

Page 1 — Overview
  KPI cards: active users, success rate, avg value
  Line chart: daily transactions
Page 2 — Risk
  Table: today's flagged transactions by rule
  Bar: flags per rule, last 30 days
Page 3 — Growth
  Funnel: sign-up to disbursal
  Heatmap: cohort retention

transactions(txn_id, user_id, txn_time, amount,
             status, channel, device_id)
loan_events(user_id, step, step_order, event_date)
users(user_id, signup_date, city, acquisition_channel)

Note: use synthetic data for portfolio work.
Never use real customer financial data publicly.