Data Analytics Jobs in Banking: Role and Responsibilities

-- Loan portfolio analysis — the core analytical query.
-- Every banking analyst writes queries like this weekly.

-- Question: what is the NPA rate by loan type and region?
-- NPA = Non-Performing Asset (loan overdue > 90 days)

WITH loan_status AS (
  SELECT
    loan_id,
    loan_type,           -- home, auto, personal, business
    region,
    disbursed_amount,
    outstanding_amount,
    days_past_due,
    CASE
      WHEN days_past_due > 90 THEN 'NPA'
      WHEN days_past_due > 30 THEN 'SMA-2'
      WHEN days_past_due > 0  THEN 'SMA-1'
      ELSE 'Standard'
    END AS asset_classification
  FROM loans
  WHERE disbursed_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
)

SELECT
  loan_type,
  region,
  COUNT(*) AS total_loans,
  SUM(disbursed_amount) / 10000000.0 AS disbursed_cr,
  SUM(outstanding_amount) / 10000000.0 AS outstanding_cr,
  COUNTIF(asset_classification = 'NPA') AS npa_count,
  SUM(IF(asset_classification = 'NPA', outstanding_amount, 0))
    / 10000000.0 AS npa_cr,
  ROUND(
    100.0 * SUM(IF(asset_classification = 'NPA', outstanding_amount, 0))
    / SUM(outstanding_amount),
    2
  ) AS npa_rate_pct
FROM loan_status
GROUP BY loan_type, region
ORDER BY npa_rate_pct DESC;

-- Sample output:
-- loan_type    region   total_loans  outstanding_cr  npa_cr  npa_rate_pct
-- personal     North    12,450       340.2           18.4    5.41
-- business     East     3,200        210.8           9.1     4.32
-- auto         South    8,100        195.4           6.2     3.17
-- home         West     6,900        520.0           8.9     1.71

-- What this teaches:
-- - CTEs for readability
-- - Asset classification logic (RBI's SMA/NPA buckets)
-- - Business-friendly output (percentages, crores)
-- - Ordering by the metric the business cares about

-- What an interviewer wants to hear:
-- "I classified loans by RBI's days-past-due buckets, then
--  computed NPA rate by loan_type and region to identify
--  the segments with the highest credit risk."

# Customer segmentation — the second core banking analysis.
# Banks want to know which customers to target and how.

import pandas as pd

# Load transactions and customer data
customers = pd.read_csv("customers.csv")
transactions = pd.read_csv("transactions.csv")

# Build a customer-level feature table
features = transactions.groupby("customer_id").agg(
    monthly_spend=("amount", lambda x: x.sum() / 12),
    txn_count_12m=("txn_id", "count"),
    avg_txn_size=("amount", "mean"),
    days_since_last_txn=(
        "txn_date",
        lambda x: (pd.Timestamp.today() - pd.to_datetime(x).max()).days,
    ),
).reset_index()

# Join customer attributes
df = customers.merge(features, on="customer_id", how="left")
df = df.fillna({
    "monthly_spend": 0,
    "txn_count_12m": 0,
    "avg_txn_size": 0,
    "days_since_last_txn": 999,
})

# Simple RFM segmentation
df["r_score"] = pd.qcut(df["days_since_last_txn"], 4, labels=[4, 3, 2, 1]).astype(int)
df["f_score"] = pd.qcut(df["txn_count_12m"], 4, labels=[1, 2, 3, 4]).astype(int)
df["m_score"] = pd.qcut(df["monthly_spend"], 4, labels=[1, 2, 3, 4]).astype(int)

# Combine into segments
def segment(row):
    r, f, m = row["r_score"], row["f_score"], row["m_score"]
    if r >= 3 and f >= 3 and m >= 3:
        return "Champions"
    if r >= 3 and m >= 3:
        return "Loyal high-value"
    if r <= 2 and f >= 3:
        return "At risk"
    if r <= 2 and f <= 2:
        return "Lost"
    return "Promising"

df["segment"] = df.apply(segment, axis=1)

# Summarise by segment
summary = df.groupby("segment").agg(
    customers=("customer_id", "count"),
    avg_monthly_spend=("monthly_spend", "mean"),
    avg_txn_count=("txn_count_12m", "mean"),
).round(1)

print(summary)

# What this teaches:
# - Feature engineering from transaction data
# - RFM segmentation — the classic banking framework
# - Business-friendly output (segment sizes, value, behaviour)
#
# Why it matters in a bank:
# Marketing uses this to decide who gets which campaign.
# Relationship managers use it to prioritise calls.
# Risk uses it to understand exposure by segment.

# Interview question:
# "How would you segment customers for a credit card campaign?"
# This is the answer.

-- Dashboard query — NPA trend report.
-- The report that every bank's risk committee looks at monthly.

-- Question: how is the NPA rate trending by month and segment?
-- (This feeds a Power BI / Tableau dashboard, not a one-off report.)

SELECT
  DATE_TRUNC(month_end_date, MONTH) AS month,
  segment,                          -- retail, SME, corporate
  COUNT(*) AS loan_count,
  SUM(outstanding_amount) / 10000000.0 AS outstanding_cr,
  SUM(IF(days_past_due > 90, outstanding_amount, 0))
    / 10000000.0 AS npa_cr,
  ROUND(
    100.0 * SUM(IF(days_past_due > 90, outstanding_amount, 0))
    / SUM(outstanding_amount),
    2
  ) AS npa_pct,
  -- Month-over-month change
  ROUND(
    100.0 * (
      SUM(IF(days_past_due > 90, outstanding_amount, 0))
      - LAG(SUM(IF(days_past_due > 90, outstanding_amount, 0)))
        OVER (PARTITION BY segment ORDER BY month_end_date)
    ) / NULLIF(
      LAG(SUM(IF(days_past_due > 90, outstanding_amount, 0)))
        OVER (PARTITION BY segment ORDER BY month_end_date),
      0
    ),
    2
  ) AS mom_npa_change_pct
FROM loans_monthly_snapshot
WHERE month_end_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
GROUP BY month, segment
ORDER BY month DESC, segment;

-- Sample output (npa_pct is what goes on the dashboard):
-- month      segment    outstanding_cr   npa_pct   mom_npa_change_pct
-- 2026-09    retail     6,240.5          2.41      +0.08
-- 2026-09    SME        1,820.4          5.12      +0.21
-- 2026-09    corporate  8,120.0          1.08      -0.04
-- 2026-08    retail     6,180.2          2.33      +0.12
-- ...

-- What this teaches:
-- - Window functions (LAG) for month-over-month comparison
-- - Cohort snapshots (month_end_date) vs live tables
-- - Aggregations that feed a dashboard, not just an answer
-- - Business-friendly output format

-- Why this matters in banking:
-- The risk committee uses this to decide credit tightening.
-- Regulators use this in quarterly reviews.
-- A single analyst owns this report — and it is visible
-- to the CEO. That is the kind of responsibility you get
-- in a bank that you would not get at a startup as a junior.