Consumer Lending Analyst Path

The portfolio is seasoning. The vintages are diverging.
Originations, HMDA, delinquency, and the SQL underneath every credit committee deck.

The consumer-lending analyst role at a regional or money-center bank: HMDA action-taken decomposition, fair-lending disparity analysis, vintage curves and first-payment-default cohorts, ARM reset wall identification, refi candidate targeting, delinquency aging and roll rates, charge-off recovery, CECL-style loss reserve decomposition, and ALCO-ready portfolio stratification — against a realistic mortgage / auto / HELOC / personal lending dataset with planted HMDA and servicing reconciliation traps.

See All Missions

30 missions · 8 tables · lending dataset on the shared retail core, with planted HMDA and servicing traps

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

Consumer Lending Scenarios

Answer questions from the Chief Credit Officer, Mortgage Sales Director, Fair Lending Compliance Officer, and Loan Servicing Lead — weekly origination dashboards, HMDA LAR reconstructions, ARM reset walls, and CECL-style loss reserves.

Authentic Lending Schema

lending_applications (HMDA-shaped), lending_originations (funded loans with FICO/DTI/LTV at orig), lending_payments (past installments with days_late), and lending_geography (census tract + LMI). Joins to the shared retail_* core for customer demographics.

HMDA + Vintage + Servicing Skills

HMDA action-taken decomposition, denial-reason rollups, LMI-tract approval gap analysis, vintage curves with cohort EXISTS, ARM reset arithmetic, roll-rate matrices, charge-off recovery ratios, and the multi-CTE rollups that real credit teams ship.

Portfolio-Ready Skills

Every mission maps to a real lending-analyst task — weekly origination packets, HMDA LAR submissions, fair-lending memos, and the SQL that lending teams at JPMC / BofA / Wells Fargo / Rocket Mortgage 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 application bookFrom: Chief Credit Officer
Loan product taxonomyFrom: Mortgage Sales Director
Funded originations this quarterFrom: Mortgage Sales Director
Applications by stateFrom: Fair Lending Compliance Officer
Net new mortgages last 30 daysFrom: Chief Credit Officer
Easy5 missions
Approval rate by productFrom: Chief Credit Officer
Avg funded amount by loan purposeFrom: Mortgage Sales Director
HMDA action_taken distributionFrom: Fair Lending Compliance Officer
30+ delinquent loan countFrom: Loan Servicing Lead
Originations by LO (top 10)From: Mortgage Sales Director
Medium5 missions
Approval rate by FICO bandFrom: Chief Credit Officer
HMDA denial-reason rollupFrom: Fair Lending Compliance Officer
DTI bucket pivot for originationsFrom: Chief Credit Officer
Pricing exception detector (rate vs benchmark)From: Mortgage Sales Director
Late-payment rate by vintage monthFrom: Loan Servicing Lead
Hard5 missions
Early-Payment Default rate by vintage monthFrom: Chief Credit Officer
ARM reset wall (next 6 months)From: Loan Servicing Lead
Refi candidate cohortFrom: Mortgage Sales Director
Delinquency progression: first miss × current statusFrom: Loan Servicing Lead
Approval gap by LMI tractFrom: Fair Lending Compliance Officer
Expert5 missions
Delinquency aging bucket pivot by productFrom: Loan Servicing Lead
Portfolio LTV stratification with seasoningFrom: Chief Credit Officer
HMDA LAR row reconstructionFrom: Fair Lending Compliance Officer
Charge-off rate + principal recovery by vintage yearFrom: Chief Credit Officer
Cumulative loss curve by vintage × months-to-charge-offFrom: Chief Credit Officer
Master5 missions
Fair Lending disparity index by FICO band (Black vs White)ProFrom: Fair Lending Compliance Officer
ALCO portfolio stratification dashboardProFrom: Chief Credit Officer
Rate-shock stress test on ARM payments (+200bp)ProFrom: Loan Servicing Lead
CECL-style loss reserve decompositionProFrom: Chief Credit Officer
Lending capstone scorecard (8-KPI single-row panel)ProFrom: Chief Credit Officer

The Database

A consumer-lending dataset on the shared retail_* core with 8 tables, ~17K lending rows, and planted HMDA/servicing traps. Mis-coded HMDA action_taken values, ARM rows missing arm_reset_date, late-posted payments coded as on-time, trailing-space loan-officer names that split GROUP BYs, and a vintage 2019 ARM cohort whose 7-year reset wall lands in the next 6 months.

retail_customers (~500)retail_products (~20)retail_branches (~25)retail_accounts (~1,200)lending_geography (~150)lending_applications (~3,030)lending_originations (~1,450)lending_payments (~11,500)

●dimension tables  ● fact tables

The credit packet is due Wednesday

Vintages have to tie, the HMDA LAR has to balance, and the CCO wants the story — in SQL.

Looking for something different?

|