Case study

SQL Playground · Lending KPIs

A single-file, in-browser SQL workbench. Recruiters land, click "Run," and watch real SQL execute against a fake-but-realistic lending portfolio — no signup, no backend, no waiting.

Open the playground → Back to work

The problem worth solving

"Show me what you can do" usually means cloning a repo, spinning up a database, loading sample data, and reading a README before a single query runs. Most reviewers never get past step one.

What I wanted to prove
Portfolio audience · 30-second window
  • Domain fluency in lending — the schema, the metrics, the patterns analysts actually look for.
  • SQL chops past the toy level — window functions, multi-CTE constructs, vintage-curve and DPD-transition math.
  • Product polish — design system, interactions, empty states, the small stuff that signals care.
  • Engineering judgment — zero backend, single file, deploys in 30 seconds, works offline once cached.
Constraints I gave myself
Frictionless by design
  • No signup, no backend, no waiting — the first query runs within seconds of page load.
  • Same data for every visitor so discoverable patterns are reproducible across sessions.
  • Single file deployable to GitHub Pages — no build step, no server, no CI required.
  • Data and queries stay in the browser; nothing leaves the device.

What's under the hood

Everything runs client-side. SQLite is compiled to WebAssembly via sql.js, so the browser holds a real relational database in memory — not a mock, not a JSON file pretending to be a table.

Stack
  • sql.js — SQLite compiled to WebAssembly; full SQL execution engine in the browser.
  • CodeMirror 5 — SQL syntax highlighting, bracket matching, keyword autocomplete.
  • Chart.js — auto-rendered line or bar charts based on result column types.
  • Vanilla JS, single file — no framework, no bundler, no build step.
  • localStorage — persistent query history that survives across sessions.
Schema (Snowflake-shaped)
  • DIM_BORROWERS — ~1,500 borrowers with FICO, state, channel, segment.
  • DIM_PRODUCTS — loan products with term, base APR, category.
  • FACT_APPLICATIONS — ~2,800 applications with status, decision reason, channel.
  • FACT_LOANS — ~1,700 funded loans with origination date, balance, APR, vintage.
  • FACT_PAYMENTS — ~28k payment records with DPD, status, amount.

Seeded data, not random noise

The data is modeled, not random. Patterns are baked in so the harder challenges don't just return rows — they reveal a story a real lending analyst would recognize.

What's modeled in
  • FICO drives default probability. Lower bands carry higher charge-off rates and higher APRs.
  • Channel funnel skew. Branch and Phone approve at higher rates than Online.
  • Vintage credit-loosening event. The 2024-Q3 cohort has a 2.2× delinquency multiplier baked in; Q4 2024 carries a milder bump.
  • Repeat-borrower behavior. Cross-sell signal is discoverable in the application history.
  • Seeded RNG. Same seed every load — every visitor sees the same data and the same discoverable patterns.
Why it matters
  • Challenge 01 (vintage delinquency drift) returns a real curve — the Q3 spike is visible, not invented.
  • Risk-tier and pricing challenges align with how an underwriter actually thinks about FICO bands.
  • Funnel challenges expose channel mix exactly the way a portfolio manager would investigate it.
  • Reproducibility means a recruiter and I can talk about the same numbers without setup.

Twenty-one challenges across six categories

Each prompt has a starter template, a full solution behind a "Show solution" button, and a written insight explaining why the result matters in real lending operations.

Categories
  • Risk — delinquency, charge-off, FICO band performance, loss curves.
  • Funnel — application-to-funding conversion, channel mix, drop-off attribution.
  • Portfolio — balance distribution, concentration, mix shift over time.
  • Behavior — repeat-borrower patterns, cross-sell signal, payment behavior.
  • Vintage — cohort curves, vintage delinquency drift, age-of-loan analysis.
  • Pricing — APR vs. risk-tier alignment, price-to-loss spread.
Difficulty tiers
  • Easy — GROUP BY, CASE expressions, basic joins. Onboarding for the schema.
  • Medium — multi-table joins, conditional aggregates, date math, CTEs.
  • Hard — window functions (ROW_NUMBER, LAG), multi-CTE constructs, JULIANDAY math for vintage curves and DPD transition matrices. CECL-allowance and roll-rate forecasting patterns — not toy problems.

The interaction layer

Pick a challenge, write or load SQL, hit Run. The result surface earns its keep through small touches that signal product care.

Result surface
  • Table — right-aligned numerics, color-coded warnings on high delinquency rates.
  • Chart — auto-detects whether to render a line or bar chart based on column types.
  • Plan — runs EXPLAIN QUERY PLAN, shows the execution tree with color-coded SCAN/SEARCH/USING INDEX keywords plus an advisory note about whether the query is index-driven.
Side features
  • CSV export of any result.
  • Copy SQL to clipboard.
  • Click any table name in the schema sidebar to instantly preview it.
  • History panel persists every run to localStorage — nothing's lost between sessions.
  • "Show solution" reveals a vetted reference query when the challenge gets stuck.

Why this works as a portfolio piece

It demonstrates four things at once that hiring managers care about — and it does it without asking the reviewer to spend ten minutes setting up an environment.

What it signals
  • Domain fluency — the schema, metrics, and seeded patterns are things a real lending analyst would recognize.
  • SQL chops — the hard challenges are CECL-allowance and roll-rate forecasting patterns, not toy problems.
  • Product polish — the design system, interactions, empty states, and small affordances all show care.
  • Engineering judgment — zero backend, single file, deploys in 30 seconds, offline-cached after first load.
What's next
  • Expand the challenge set with stress-tested rate-shock and prepayment-curve prompts.
  • Add a "share my answer" link that encodes the user's SQL into the URL for review.
  • Surface a leaderboard of canonical solutions vs. user attempts (still client-only, still no backend).
  • Open the schema-and-seed pattern to other domains — collections recovery, fraud signal, servicing.

Try the playground

Open it, pick a challenge, hit Run. The whole point is that there's nothing to install and nothing to wait for.

Open SQL Playground → Back to portfolio