Rank banks by UPI downtime with NPCI incident data

The table you are working with

On NPCI's UPI Product Statistics there is a Downtime/Incidents tab next to Daily/Monthly Statistics and Uptime. The table we observed has four columns:

• Sr. No.
• Name of the Member Bank
• Incident Count
• Downtime in hours, written as hours and minutes (for example "02:28")

The page offers a Download control. Whether the export keeps the same headers as the on-screen table is something to check in the downloaded file's header before you trust your parser. The page does not say how an "incident" is defined or who attributes it to a bank. Treat the figures as NPCI's published attribution, and say so.

Why this belongs in an FDE portfolio

A forward deployed engineer spends much of their time connecting a client's messy, changing sources to something that works every day. This project has all the ingredients on a small scale:

• an external source you do not control
• a duration format that trips up naive parsing
• entity names that drift
• the need to rerun without creating duplicate rows
• a stakeholder question ("which banks are unreliable?") that needs care to answer fairly

That is a better talking point than another notebook of charts. Our Forward Deployed Engineer page explains the role in more depth.

Design before code

A pipeline core that runs on synthetic rows

The rows below are synthetic. The bank names are placeholders, not real banks, and none of the durations are NPCI figures.

import sqlite3
import pandas as pd

def to_minutes(hhmm: str) -> int:
    h, m = hhmm.strip().split(":")
    if not (h.isdigit() and m.isdigit()) or int(m) >= 60:
        raise ValueError(f"bad duration: {hhmm!r}")
    return int(h) * 60 + int(m)

assert to_minutes("02:28") == 148
assert to_minutes("10:05") == 605

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE bank_alias (raw_name TEXT PRIMARY KEY, bank TEXT NOT NULL);
CREATE TABLE snapshot (
  period TEXT, bank TEXT, incidents INTEGER, minutes INTEGER, raw_duration TEXT,
  loaded_at TEXT, PRIMARY KEY (period, bank));
""")
con.executemany("INSERT INTO bank_alias VALUES (?, ?)", [
    ("Alpha Bank", "Alpha Bank"), ("Alpha Bank Ltd", "Alpha Bank"),
    ("Beta Co-operative Bank", "Beta Co-op"), ("Gamma Bank", "Gamma Bank")])

def load(period, rows, loaded_at):
    for raw_name, incidents, duration in rows:
        bank = con.execute("SELECT bank FROM bank_alias WHERE raw_name = ?", (raw_name,)).fetchone()
        if bank is None:
            raise KeyError(f"unmapped bank name: {raw_name!r}")
        con.execute("""
            INSERT INTO snapshot VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(period, bank) DO UPDATE SET
              incidents = excluded.incidents, minutes = excluded.minutes,
              raw_duration = excluded.raw_duration, loaded_at = excluded.loaded_at""",
            (period, bank[0], incidents, to_minutes(duration), duration, loaded_at))

load("2026-07", [("Alpha Bank", 2, "02:28"), ("Beta Co-operative Bank", 6, "10:26")], "2026-08-02")
load("2026-08", [("Alpha Bank Ltd", 1, "00:45"), ("Gamma Bank", 3, "03:44")], "2026-09-02")
load("2026-08", [("Alpha Bank Ltd", 1, "00:50"), ("Gamma Bank", 3, "03:44")], "2026-09-05")  # a revision

print(con.execute("SELECT COUNT(*) FROM snapshot").fetchone()[0], "rows after rerun")

league = pd.read_sql_query("""
    SELECT bank, COUNT(DISTINCT period) AS periods_listed,
           SUM(incidents) AS incidents, SUM(minutes) AS minutes,
           ROUND(1.0 * SUM(minutes) / SUM(incidents), 1) AS minutes_per_incident
    FROM snapshot GROUP BY bank ORDER BY minutes DESC""", con)
print(league)

Three details carry the design. The duration parser rejects "02:75" instead of silently producing nonsense. An unmapped bank name stops the load, because a new spelling is exactly the kind of drift you want to see. The rerun on 5 September updates in place, which is why the table ends with four rows rather than six.

What "fair" means for a ranking

Raw downtime minutes favour tiny banks, which have fewer transactions to disrupt, and punish large ones. Minutes per incident distinguish many short blips from rare long outages. Neither is a failure rate.

NPCI's ecosystem statistics page has a "Top 50 Member Performance" tab, which may let you relate downtime to each bank's activity. Check its downloaded header to see what it actually reports before building a rate on it. If the join is not reliable, present the two measures side by side and state that the table is not volume-adjusted.

Also note what is absent. A bank missing from the downtime table in a month may have had no incidents, or the table may list only banks above some threshold. The page does not say, so do not code absence as zero without writing that assumption down.

The finished deliverable

• A pipeline.py with download, parse, load and report steps, and a README showing how to rerun it.
• Tests for duration parsing, alias mapping and idempotent reload.
• A monthly report: a league table on both measures, plus a chart of each bank's minutes across months, limited to banks listed in at least three periods.
• A "revisions" section that lists any past period whose figures changed between downloads.

Interview questions to expect

1. 1Why store minutes as integers and keep the raw string?
2. 2What happens when NPCI renames a column next quarter?
3. 3How would you schedule and monitor this so a silent failure is noticed?
4. 4Your ranking says bank X is worst. What would you tell that bank's product team, and what would you not claim?
5. 5How would you add the uptime tab without duplicating logic?

Building dependable integrations like this, and explaining their limits to non-engineers, is what the FDE for Freshers programme trains. Assured job interviews through our hiring network, plus a post-course internship, are available for eligible learners meeting attendance and capstone criteria.