Katkat
Back

Reconcile Monthly Bank-Account Balances Across Two Datasets

StripeSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

Implement a reconciliation function that joins internal ledger balances with external bank statements for a target month, categorizing discrepancies while strictly distinguishing between missing records and zero balances.

Problem Statement

Financial platforms regularly reconcile internal accounting ledgers against external bank statements to detect discrepancies and missing transactions.

You are given two datasets representing monthly account snapshots:

  1. internal_records: Account balances recorded in the internal ledger.
  2. bank_records: Account balances reported by partner banking institutions.

Each record is a dictionary containing:

  • "account_id" (str): Unique account identifier.
  • "month" (str): Calendar month in "YYYY-MM" format.
  • "balance_cents" (int): Closing balance in cents (can be positive, zero, or negative).

Implement the function reconcile_monthly_balances(internal_records, bank_records, target_month) to compare balances for the given target_month. Each account appears at most once per dataset for any given month.

For every account that appears in either dataset for the specified target_month, produce a reconciliation result dictionary with:

  • "account_id" (str)
  • "internal_balance_cents" (int or None if missing)
  • "bank_balance_cents" (int or None if missing)
  • "status" (str): One of the following:
    • "MATCH": Both records exist and have identical balances.
    • "MISMATCH": Both records exist but balances differ.
    • "MISSING_BANK": Internal record exists, but bank record is missing.
    • "MISSING_INTERNAL": Bank record exists, but internal record is missing.

Return the results sorted lexicographically by account_id.

Function Signature

def reconcile_monthly_balances(
    internal_records: list[dict],
    bank_records: list[dict],
    target_month: str
) -> list[dict]:
    pass

Example

internal = [
    {"account_id": "acc_1", "month": "2024-03", "balance_cents": 10000},
    {"account_id": "acc_2", "month": "2024-03", "balance_cents": 0},
    {"account_id": "acc_3", "month": "2024-03", "balance_cents": 5000},
    {"account_id": "acc_1", "month": "2024-02", "balance_cents": 9000},
]

bank = [
    {"account_id": "acc_1", "month": "2024-03", "balance_cents": 10000},
    {"account_id": "acc_2", "month": "2024-03", "balance_cents": 0},
    {"account_id": "acc_4", "month": "2024-03", "balance_cents": 0},
    {"account_id": "acc_3", "month": "2024-02", "balance_cents": 5000},
]

reconcile_monthly_balances(internal, bank, "2024-03")
# Output:
# [
#     {"account_id": "acc_1", "internal_balance_cents": 10000, "bank_balance_cents": 10000, "status": "MATCH"},
#     {"account_id": "acc_2", "internal_balance_cents": 0, "bank_balance_cents": 0, "status": "MATCH"},
#     {"account_id": "acc_3", "internal_balance_cents": 5000, "bank_balance_cents": None, "status": "MISSING_BANK"},
#     {"account_id": "acc_4", "internal_balance_cents": None, "bank_balance_cents": 0, "status": "MISSING_INTERNAL"}
# ]

Constraints

  • 0 <= len(internal_records), len(bank_records) <= 100,000
  • balance_cents is an integer in the range [-10^12, 10^12]
  • Target month and all record months follow valid "YYYY-MM" formatting
  • Do not treat a balance of 0 as None / missing

Hints

Hint 1

Filter both datasets by `target_month` first and map `account_id` to `balance_cents` using hash maps.

Hint 2

Be careful with falsy checks in Python: `if balance:` will evaluate to `False` when `balance == 0`, so explicitly check `balance is not None` or membership in the map.

Hint 3

Form the union of all unique account IDs across both filtered datasets, sort them, and resolve the status based on key presence.

Follow-up questions

  • How would you scale this reconciliation if both datasets contain 100 million records and cannot fit in memory on a single machine?
  • If currency conversions or exchange rate fluctuations can cause slight penny-level discrepancies, how would you allow configurable tolerance thresholds (e.g., tolerance in cents or percentages)?
  • How would you write this equivalent operation in SQL using standard joins?