Retail Banking Operations Path

The branch deposits are the bank.
Deposits, branch ops, NSF/OD, ATM economics, and treasury — the SQL underneath every retail banking P&L.

The retail-banking analyst role at a regional or money-center bank: deposit growth decomposition, deposit beta on MMAs, NSF/OD fee revenue post-CFPB, branch consolidation scoring, NII decomposition, primary-banking-relationship classification, churn leading indicators, and FTP-allocated branch P&Ls against a realistic retail bank with planted reconciliation traps.

See All Missions

30 missions (estimated) · 9 tables · retail bank core with planted ledger and snapshot mess

How It Works

1

Read the briefing

A Slack message from your manager

2

Explore the schema

5 tables in a star schema

3

Write your query

Full SQL editor with autocomplete

4

Get expert feedback

Graduated hints, not just pass/fail

Why This Path

Retail Banking Scenarios

Answer questions from the Retail COO, Branch Ops Manager, Treasurer, and Customer Insights Lead — weekly retail dashboards, ALCO-ready deposit beta, NSF/OD scenario modeling, and branch P&L with FTP allocation.

Authentic Retail Bank Schema

6-table retail_* core (customers, accounts, products, branches, transactions, daily balances) plus retailops_* specialty tables for branch activity, fees, and ATM events. Numbers anchor to 2024-2026 retail banking realities.

Deposit + Branch + Treasury + Customer Skills

Average daily balance, weighted deposit beta, primary-relationship EXISTS logic, churn lag features, FTP curve allocation, and the multi-CTE rollups that real retail-bank dashboards run on.

Portfolio-Ready Skills

Every mission maps to a real retail-banking-analyst task — weekly retail packs, ALCO submissions, branch-strategy memos, and the SQL that retail teams at JPMC / BofA / PNC actually screen for.

The Missions

Each mission is a real request from someone at the company. Difficulty increases as you go.

Starter5 missions
1
Peek at the customer bookFrom: Retail COO
Product taxonomy inventoryFrom: Retail COO
Open accounts right nowFrom: Branch Ops Manager
Active deposit customers by stateFrom: Customer Insights Lead
Net new checking accounts this weekFrom: Retail COO
Easy4 missions
Branch 13 missing transactionsFrom: Branch Ops Manager
CD maturity wall (next 90 days)From: Treasurer
NSF / OD fee revenue by FICO bandFrom: Retail COO
ATM in-network vs foreign economicsFrom: Branch Ops Manager
Medium5 missions
Primary banking relationship classificationFrom: Customer Insights Lead
Deposit beta on MMAs (cycle-to-date)From: Treasurer
Rate-tier balance distributionFrom: Treasurer
Average daily balance for fee waiversFrom: Branch Ops Manager
Dormant account audit (180+ days silent)From: Customer Insights Lead
Hard6 missions
Onboarding funnel: 90-day primary-relationship rateFrom: Customer Insights Lead
Monthly interest income by product categoryFrom: Treasurer
Branch consolidation candidate scoreFrom: Retail COO
2025 closed-account exit cohort with tenureFrom: Customer Insights Lead
Loan-to-Deposit ratio by regionFrom: Treasurer
Cross-sell candidate model: cash-back cardFrom: Customer Insights Lead
Expert5 missions
Deposit balance by acquisition channelFrom: Retail COO
Tier-break top-up candidates (Savings/MMA)From: Treasurer
FDIC insurance bucket stress testFrom: Treasurer
Reg E dispute aging reportFrom: Branch Ops Manager
Customer 360 — top depositorsFrom: Customer Insights Lead
Master5 missions
Branch-level P&L with FTP overlayProFrom: Retail COO
Deposit beta by product subcategoryProFrom: Treasurer
NSF/OD fee cap scenario by FICO bandProFrom: Retail COO
Treasury daily liquidity dashboardProFrom: Treasurer
Retail Ops capstone scorecardProFrom: Retail COO

The Database

A retail-bank star schema with 11 tables, 60K+ rows, and planted reconciliation traps. Trailing-space vendor names that split GROUP BYs, miscoded products, weekend snapshot gaps, late-posted transactions, joint-account dupes, and test accounts that need filtering before anything ties to the GL.

retail_customers (~500)retail_accounts (~1,200)retail_products (~20)retail_branches (~25)retail_transactions (~30,000)retail_balance_daily (~50,000)retailops_branch_activity (~2,250)retailops_fees (~5,000)retailops_atm_events (~5,000)retailops_disputes (~40)retailops_ftp_rates (~6)

●dimension tables  ● fact tables

The retail packet is due Monday

Deposit beta has to tie, the branch P&L has to net to the GL, and the COO wants the story — in SQL.

Looking for something different?

|