Approval and After

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4
Asked in
Block

Problem

We run a consumer lending platform. A customer can apply many times, but each application follows one path: our risk team approves or declines it, an approval produces exactly one offer, and that offer is either accepted (funding a single loan) or left to lapse. Because a customer's credit profile drifts over time, the analytics team wants approval rates broken down by the segment the applicant was in when they applied. Design the data model.

Approved, declined, or pending. Design the tables that say so.

Worked solution and explanation

What this really is

Under the lending story this is a grain problem with a time-travel trap inside it. Two things are being probed. Can you keep applied, approved, offered and funded as separate events instead of one wide row? And can you freeze the applicant's segment on the application rather than reading it off the live customer? Anyone can draw four boxes. What separates candidates is where credit_score_at_apply lives. Join today's customers row instead, and every historical approval rate quietly rewrites itself the next time a score moves. Merge approval with funding and conversion inflates the same quiet way.

Building it

Step 1: Give each funnel event its own table

loan_applications, loan_offers and funded_loans each get a surrogate BIGINT key at their own grain. A merged table carries nullable offer and funding columns on every declined row. That breaks 3NF, and the drop-off math turns into counting NULLs.

Step 2: Snapshot the segment on loan_applications

credit_score_at_apply and stated_income_at_apply sit on the application row. It looks like denormalization, but it is not a transitive dependency. The value depends on application_id, because it was true at that moment, and not on customer_id.

Step 3: Put the foreign key on the child

loan_applications.customer_id is many-to-one. loan_offers.application_id and funded_loans.offer_id are one-to-one, enforced by a unique constraint on each FK. A declined application or a lapsed offer simply has no child row, so nothing is nullable to fake it.

Step 4: Let status carry the decision

Constrain status to 'pending', 'approved' and 'declined' with a CHECK. Fill decline_reason only when the status is 'declined'. Approval is a state of the application. Funding is a row in its own table.

One FK per event step; the segment snapshot lives on `loan_applications`
  • customers: customer_id BIGINT PK, email TEXT, dob DATE, signup_date DATE
  • loan_applications: application_id BIGINT PK, customer_id BIGINT FK, applied_at TIMESTAMP, requested_amount DECIMAL, credit_score_at_apply INT, stated_income_at_apply DECIMAL, status TEXT, decline_reason TEXT
  • loan_offers: offer_id BIGINT PK, application_id BIGINT FK, offered_amount DECIMAL, apr DECIMAL, term_months INT, expires_at TIMESTAMP
  • funded_loans: funded_loan_id BIGINT PK, offer_id BIGINT FK, funded_at TIMESTAMP, principal DECIMAL, rate DECIMAL
  • loan_applications.customer_id references customers.customer_id (N:1)
  • loan_offers.application_id references loan_applications.application_id (1:1)
  • funded_loans.offer_id references loan_offers.offer_id (1:1)

Proving the model answers the question

Funnel by credit tier at application time
SELECT
    CASE
        WHEN a.credit_score_at_apply >= 740 THEN 'prime'
        WHEN a.credit_score_at_apply >= 670 THEN 'near_prime'
        ELSE 'subprime'
    END AS tier,
    COUNT(*) AS applications,
    COUNT(*) FILTER (WHERE a.status = 'approved') AS approved,
    COUNT(f.funded_loan_id) AS funded
FROM loan_applications a
LEFT JOIN loan_offers o ON o.application_id = a.application_id
LEFT JOIN funded_loans f ON f.offer_id = o.offer_id
WHERE a.applied_at >= NOW() - INTERVAL '90 days'
GROUP BY tier
ORDER BY tier

Tiers come from credit_score_at_apply; customers is never joined

The query works only because the joins are one-to-one. The LEFT JOINs keep declined and lapsed rows, so COUNT(*) stays at application grain while COUNT(f.funded_loan_id) counts only the rows that reached funding. Allow two offers per application and applications double-counts silently.

Snapshot on the application
credit_score_at_apply is frozen on the row. Backtests are deterministic and the query needs no temporal join. The cost is one copied column per input.
Type 2 customer dimension
A history table with valid_from and valid_to. You get one source of customer state, but every funnel query needs a range join on applied_at, and a late correction rewrites history.

Common follow-up questions