CECL lifetime loss projection by vintage

Credit analysts don’t get to ship a wrong number

A consumer-banking star schema — customers, accounts, transactions, applications, and monthly delinquency snapshots — with planted risk-reporting traps: reversals that inflate raw spend, customer-vs-account grain gotchas, charged-off accounts still posting recoveries, FICO-at-origination vs current, and banking-day vs calendar-day conventions.

Query a consumer-banking data model with planted risk-reporting traps, build the vintage curves, roll-rate matrices, fraud rules, and CECL loss projections that credit-risk, fraud, AML, and regulatory teams actually write every month. Shaped around the Capital One / JPM / BofA analyst interview loop and the work mandated by CCAR, CECL, BSA, and SR 11-7.

Vintage analysis, roll rates, delinquency bucketing, FICO-band segmentation, and PD/LGD/EAD feature engineering — the queries every credit analyst writes.

Credit Risk Mechanics

Velocity features, structuring detection, rolling-window rules, and composite risk scoring — patterned on NICE Actimize / SAS AML / FICO Falcon workflows.

Fraud + AML Patterns

Point-in-time quarter-end snapshots, Y-9C / Call-Report-style aggregations, CECL lifetime-loss projection, and PSI / KS model-monitoring queries.

Regulatory Reporting

Every mission maps to a live Cap One Power Day prompt, a JPM Analyst Development Program case, or a Progressive / fintech fraud-strategy scenario.

Interview-Ready Skills

The 30+ DPD rate ticked up.

Peek at the customer book

What products do we book?

Open accounts right now

Active deposit customers by state

Customer vs account — know the grain

Reversals and chargebacks — clean the spend total

Delinquency bucket snapshot

FICO band distribution at origination

Approval rate by FICO band

Joint accounts — who’s the primary?

Monthly net charge-off rate by product

Vintage originations pacing

Velocity feature — auths in 1 hour

Structuring flag — sub-$10K cash deposits

Dormant account audit

Vintage curve — 90+ DPD by MOB

Roll-rate transition matrix

Rolling velocity — ≥5 auths in 10 minutes

Same-day amount-swing flag

Quarter-end balance snapshot (Y-9C style)

PSI — score distribution vs baseline

KS statistic on PD model scores

Champion–challenger collections backtest

Portfolio risk scorecard capstone

Charge-off rate by product — FICO band heatmap

Vintage delinquency drift YoY (12-MOB)

Chargeback risk score by merchant category

Product portfolio risk summary

CRO board scorecard capstone

customer_id

first_name

last_name

date_of_birth

state

onboarded_date

is_primary_only

banking_customers

account_id

product_type

open_date

close_date

credit_limit

current_fico

status

banking_accounts

txn_id

posted_date

amount

merchant_category

channel

is_reversal

is_chargeback

banking_transactions

app_id

app_date

decision

fico_at_app

offered_apr

approved_limit

booked_account_id

banking_applications

snapshot_id

snapshot_date

days_past_due

balance

is_charged_off

recoveries

banking_delinquency

CECL lifetime loss projection by vintage

The Brief

You'll practice